0

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
David
  • 239
  • 3
  • 10
  • I can understand the requirement to remove a set of characters such as "special" (although you still have to define what special is. Is "." special?). BUT, you cannot request ro remove *formatting* from a string. Because strings in sql do not contain formatting *in the first place*. You even had to use an image to show this here - you surely won't manage to fit a red subscript into a tsql string. – George Menoutis Jan 29 '20 at 23:29
  • Hello George, I editted my question, changing special character to 'non-alphanumeric' characters. However, the superscript/subscript can be seen and saved in SQLServer. Please see example above. – David Jan 29 '20 at 23:44
  • Does this answer your question? [How to strip all non-alphabetic characters from string in SQL Server?](https://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server) – CR241 Jan 30 '20 at 00:20

2 Answers2

1

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

David
  • 239
  • 3
  • 10
Mani Live
  • 168
  • 1
  • 7
  • David, thanks for editing and including the upper case A-Z, but regex '%[^a-zA-Z0-9\-]%' was not removing the backslash for me from the string. For some reason backslash is not required to escape '-'. Did you try with a backslash in your input string? – Mani Live Jan 30 '20 at 23:13
0

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
            ...
David
  • 239
  • 3
  • 10