SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Identify Bad Characters in a Table Column

By Dwain Camps,

This script uses a recursive CTE to identify non-allowed characters from a column in a table.  In this case, the PATINDEX is set up to consider alphanumerics as the only valid characters.

You may need to set the MAX RECURSION limit if you think there will be > 100 invalid characters in any particular string.

I have tested this script using a 100,000 row test harness on 100 character strings salted with about 20-25% non-alphanumeric characters and it ran in about 28-45 seconds.  1,000,000 rows ran in about 5-6 minutes.  Variability was probably caused by random invalid characters being assigned to each string.

[Note to editor] Sorry but I couldn't figure out how to get my script into the field for it on this page. 

Total article views: 2497 | Views in the last 30 days: 1
Related Articles

Trim Non-Alpha characters from string

Trim Non-Alpha characters from string


Random 64 Characters alphanumeric String

Random 64 Characters alphanumeric String



No, I’m not talking about a Dickens novel. I’m talking about the number of characters in a string....


Script to replace 5 consecutive digits with special character

In a given input string, The script searches for 5 consecutive digits and replaces it with a special...


ASCII Character Groups in String

Powerful function to find strings containing or excluding classes of ASCII characters.