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: http://jamesveitch.com/t-sql-function-replace-non-printable-ascii-chars/
other solutions based on PatIndex: http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server
... 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
Good example is shown here: http://jamesveitch.com/t-sql-function-replace-non-printable-ascii-chars/
other solutions based on PatIndex: http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server
... 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:
Post a Comment