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)
Thank this author by sharing:
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.
Trim Non-Alpha characters from 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....
In a given input string, The script searches for 5 consecutive digits and replaces it with a special...
Powerful function to find strings containing or excluding classes of ASCII characters.