Friday, February 6, 2015

find non-printable characters in nvarchar string (T-SQL)

Yesterday I needed to find all characters in an nvarchar which are non-alpha-numeric... long story short... I found no better way than writing my own stored function and walk over the string char by char.

Good example is shown here:

other solutions based on PatIndex:

... the only adaption needed to the function of the first link is in the if clause.

First you don't have to write hard coded numbers... there is a tsql-function called "ascii" which can be used instead (e.g.: ascii('A') => 65) AND is even used in the function to convert the current char to a number... a second function "unicode" does the same, but for unicode (n)chars.

Second: use begin and end statement and check explicitly for null

Third: adapt solution for language (e.g.: german öäüß,...) ...

kr Daniel

No comments: