Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
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: 2432 | Views in the last 30 days: 12
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.


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones