How to remove all special characters from a column name string using regular expressions

Question

I want to remove all the special characters like $, %, #, etc from the column name of my table in SQL Server 2018. how to do it with the help of regular expressions?

If the value in my column is "S$d#@gh", the output should be -- "Sdgh"

Thank you.

Answer 1

Try This:

DECLARE @str VARCHAR(400)='S$d#@gh'
DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!]%'

WHILE PATINDEX( @expres, @str ) > 0
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')
SELECT @str

You can Create a SCALAR FUNCTION & pass this Column

CREATE FUNCTION dbo.Remove_SpecialCharacters( @str VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS
BEGIN
DECLARE @expres  VARCHAR(50) = '%[~,@,#,$,%,&,*,(,),.,!]%'

WHILE PATINDEX( @expres, @str ) > 0
SET @str = Replace(REPLACE( @str, SUBSTRING( @str, PATINDEX( @expres, @str ), 1 ),''),'-',' ')
RETURN @str
END

O/P

SELECT dbo.Remove_SpecialCharacters(COLUMNNAME),* FROM TABLENAME

Answer 2

SQL Server now supports translate(). This allows you to do:

select replace(translate(column, ',~@#$%&*().!', replace(' ', 12)), ' ', '')

The only trick is that the replacement string of spaces needs to be exactly the same length as the characters you are looking for.