Search code examples
c#sqlweb-servicessql-server-2008clarion

Remove unknown hidden characters from string

I have an application (written in Clarion) that accepts user input and that input gets saved to a sql database. Then those records get processed and sent to a server via web service (written in C#). But sometimes the web service gives a bad request exception on text that contains invalid characters and are invisible in sql.

When I copy paste the sql field into notepad++ it shows the 'character'.

invalid chars

We suspect this issue occurs when a user copy and pasted text from Outlook in the input field.

How can I handle this problem? I cant use text.replace as I don't know what to search for in the first place. Is there an all round remove invalid characters from string or a convert to plain text function? But things as carriage return should remain. Some uni-code 16 to uni-code 8 conversion maybe?

A little lost on this one. Thanks

Solution

  • US is Unit Separator. This is 0x1f or 31. You may try 

    REPLACE(yourField,char(31),'')