Find columns with special characters



im specialImagine a database where you want to know how many times a certain column contains a special character. The reason I had to do this was because I needed to know the impact of an export interface which wasn’t working properly with special characters.

But as lazy as I am as it comes to do repetitive tasks, I had to find a way I could easily get all columns with special characters in a data set without a lot of hassle.

The solution

Fortunately for me I can use the internal INFORMATION views of a database in SQL Server to get all the columns with their table, schema and type of data. Because this database only used varchar columns for saving text data I only had to search for this data type. You can easily extend the selecting by adding the neccesary types like TEXT, NVARCHAR etc.

To create the script I had to accomplish the following things:

  1. Retrieve all the colums that could contain text
  2. Set up a pattern to check the columns with
  3. Loop through the columns
  4. In every loop save the results
  5. Make up a data set of the data retrieved

I eventually got to this script:

This might take a while when you need to search through a lot of columns and records.  When the script you will get a result like below:

results specialcharacters

results specialcharacters








The pattern can contain any character that you want. I only searched for some of the most common special characters but any character will do.

I hope you find this post helpfull and I appreciate any comment.