Search code examples
sqlsql-serverxmlsql-server-2008special-characters

Remove special/unprintable char from data

I have special chars in my sql tables and want to remove the special chars.

I have a function that strips out all chars that breaks XML.

fn_RemoveBadXMLChars(data)

which does something like this:

REPLACE(@ResultVar,'ÿ','')

Is there any easier way to remove every special chars instead of have 100 replace statments?

Solution

  • You can create a filter table that contains a record for each of the values you want to replace, like this:

    CREATE TABLE FilterTable (
    FilterCharacter nchar(1) primary key
)

INSERT INTO FilterTable SELECT 'ÿ'
INSERT INTO FilterTable SELECT 'o'

    And then use a variable to replace against your filter table like this:

    DECLARE @ResultVar nvarchar(50)
SET @ResultVar = 'Bobbÿ'

SELECT @ResultVar = REPLACE(@ResultVar, FilterCharacter, '')
FROM FilterTable   

SELECT @ResultVar