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:
--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
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.
--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
Popular search terms:
You can use the trick I describe here to to replace invalid characters
Nice.. thank you.