'Removing Junk Character from a string

I have a SQL statement in a case condition in one of the query which determines if a record is a pass or fail.

The condition is like below:

CASE
    WHEN PATINDEX('%[^a-zA-Z0-9 [-^+!#.,&''""():/-]%', [NAME1]) = 0 
        THEN 'Pass'
        ELSE 'Fail'
    END AS RESULTS

meaning, we are checking for any junk chars in the string, but there are some hidden chars in some of the strings between two words (in the space) like ex: U+A0 (got to know this from an online tool check)

Now, how do I include this check in the above condition which I have? I tried the COLLATE function which I saw in this group, but either I'm not using it correctly or its not working.

Could someone help me on how I can achieve this?

Thanks.

sqlsql-server


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions