CODEWITHSUNDEEP
sql-servert-sql
David
David

Reputation: 279

Function to remove all Non-alpha-numeric characters, superscripts, and subscripts, except a dash '-'

I need to create a T-SQL function that only keeps a hyphen (dash '-') and removes all non-alphanumeric characters (plus all spaces, superscripts and subscripts) from a given string.

You can test Superscript/Subscripts in SSMS:

select  'Hello® World™ '

Example:

input string
enter image description here

output string:

HelloWorld-ThisIsATest123

Any solutions or thoughts will be appreciated.

Upvotes: 0

Views: 3790

Answers (2)

Mani Live
Mani Live

Reputation: 178

Check this link. This removes all alpha numeric characters. You can include '-' also to the included list.

How to strip all non-alphabetic characters from string in SQL Server?

In this example for the answer from @George Mastros, use '%[^a-zA-Z0-9-]%' for regular expression instead of '%[^a-z]%'

Here is the reformatted function to include '-' and numeric characters:

-- Reformatted function
Create Function [dbo].[RemoveNonAlphaCharacters](@Temp VarChar(1000))
Returns VarChar(1000)
AS

Begin
Declare @KeepValues as varchar(50)
Set @KeepValues = '%[^a-zA-Z0-9\-]%' 
While PatIndex(@KeepValues, @Temp) > 0
    Set @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

Return @Temp
End

--Call function    


 Select dbo.RemoveNonAlphaCharacters('Hello® World™   -123 !@#$%^')

OUTPUT: HelloWorld-123

Upvotes: 1

David
David

Reputation: 279

I identified my code's issue - I previously had exact same function which was NOT removing superscript / subscript, and I was wondering why. Here was the issue: The input/output datatype should NOT be NVARCHAR , but mere varchar, else it will contain superscripts in the return string:

problem code :
    Create Function [dbo].[RemoveNonAlphaCharacters](@Temp NVarChar(1000))
           Returns NVarChar(1000)
           AS
            ...

Upvotes: 0

Related Questions