Replace Invalid Characters

Unfortunately I haven’t found a way to remove invalid characters without looping. If anyone knows a way please post.

This script will remove invalid characters:
[cc lang=”sql”]
–Leave only numbers
WHILE PATINDEX(‘%[^0-9]%’,@string) > 0
BEGIN

SET @pos = PATINDEX(‘%[^0-9]%’,@string)
SET @string = REPLACE(@string,SUBSTRING(@string,@pos,1),”)

END

–Leave only characters
WHILE PATINDEX(‘%[^a-z]%’,@string) > 0
BEGIN

SET @pos = PATINDEX(‘%[^a-z]%’,@string)
SET @string = REPLACE(@string,SUBSTRING(@string,@pos,1),”)

END
[/cc]

In order to replace characters for all columns in your table, you can use the following script. Simply replace the value “Old_Column” with your column name.
[cc lang=”sql”]
–Leave only numbers
SELECT New_Column = REPLACE(Old_Column,SUBSTRING(Old_Column,PATINDEX(‘%[^0-9]%’,Old_Column),1),”)
INTO #YourNewResults
FROM YourTable

–Leave only characters
SELECT New_Column = REPLACE(Old_Column,SUBSTRING(Old_Column,PATINDEX(‘%[^a-z]%’,Old_Column),1),”)
INTO #YourNewResults
FROM YourTable
[/cc]