Post reply

Script to remove any foreign characters

  • September 14, 2015 at 8:29 am

    #304501

    Hello,

    I have a table that is riddled with weird characters. So far I have found an escape character for PDF files and a trademark sign. These characters are crashing my SSIS packages. I am able to remove these characters with an update script...

    Update TABLE

    set LEAD_NOTES__C = Replace(LEAD_NOTES__C, nchar(65533) COLLATE Latin1_General_BIN2, '!');

    Update TABLE

    set LEAD_NOTES__C = Replace(LEAD_NOTES__C, nchar(1671) COLLATE Latin1_General_BIN2, '!');

    This works fine, but my question is...

    I would like to write a script that removes all foreign characters with the exception of the normal characters like (@,#,$,%,etc). I need a dynamic process that handles this so I am not losing time sifting through over 20,000 rows of data and changing my update script to remove a specific column. Although this method works, I would prefer a dynamic query. I intend to wrap this in a stored procedure that loops through all columns in a table (as parameter).

    Any help would be greatly appreciated.

    STG

    The are no problems, only solutions. --John Lennon

  • September 14, 2015 at 8:38 am

    #1826383

    Hopefully someone has an answer, the only ones I can think of are LOOP based.

    You can find rows that have extended characters with

    WHERE MyColumn LIKE '%[^ -~]%'

    You might need a BINARY Collation (or perhaps an Accent Sensitive one) to prevent foreign accents being treated the same as regular characters.

    but that just tells you there is an extended character, not its ASCII value. I would then use PATINDEX for that job, but you have to find the first character, fix it, then find the next ... which won't scale well.

  • September 14, 2015 at 8:51 am

    #1826395

    Have you tried something like this?

    Update TABLE

    set LEAD_NOTES__C = CAST( LEAD_NOTES__C AS varchar( 8000)) --Or whatever length you need.

    This will remove unicode characters and replace them with a question mark (?).

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 14, 2015 at 9:12 am

    #1826404

    Ignore this post please.

    The are no problems, only solutions. --John Lennon

  • September 14, 2015 at 9:34 am

    #1826419

    This is not what I had planned but it worked!

    Thanks Luis 😀

    The are no problems, only solutions. --John Lennon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply