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('%[^0-9]%',@string)
SET @string = REPLACE(@string,SUBSTRING(@string,@pos,1),'')
END
You can use the trick I describe here to to replace invalid characters
Nice.. thank you.