Searching for non-ASCII characters using SQL Server Searching for non-ASCII characters using SQL Server

This is a handy little bit of SQL when you want to find rows in a specific table that have non-ASCII characters. In the following example, you specify the table in your database and the code will search all rows in that table and all nvarchar columns with non-ASCII characters leveraging the SQL substring between two characters.

The output will be a list of the table field names and what invalid characters they are.

Let's take a look at the code.

@sql    varchar(max)    = ''
,@table sysname         = 'customer' -- enter your table here
;with ColumnData as (
RowId               = row_number() over (order by c.COLUMN_NAME)
,ColumnName         = '[' + c.COLUMN_NAME + ']'
,TableName          = '[' + c.TABLE_SCHEMA + '].[' + c.TABLE_NAME + ']'
c.DATA_TYPE         = 'nvarchar'
and c.TABLE_NAME    = @table
@sql = @sql + 'select FieldName = ''' + c.ColumnName + ''',         InvalidCharacter = [' + c.COLUMN_NAME + ']  from ' + c.TableName + ' where ' + c.ColumnName + ' collate LATIN1_GENERAL_BIN != cast(' + c.ColumnName + ' as varchar(max)) '  +  case when c.RowId <> (select max(RowId) from ColumnData) then  ' union all ' else '' end + char(13)
ColumnData c
-- check
-- print @sql
exec (@sql)

The example above usess nvarchar but can be easily adapted to varchar as well by changing this line:

c.DATA_TYPE = 'nvarchar'

Published on Jun 10, 2022

Tags: SQL Tutorials for Beginners, Intermediate and Advanced Users

Related Posts

Did you enjoy this article? If you did here are some more articles that I thought you will enjoy as they are very similar to the article that you just finished reading.


Learn how to code in HTML, CSS, JavaScript, Python, Ruby, PHP, Java, C#, SQL, and more.

No matter the programming language you're looking to learn, I've hopefully compiled an incredible set of tutorials for you to learn; whether you are beginner or an expert, there is something for everyone to learn. Each topic I go in-depth and provide many examples throughout. I can't wait for you to dig in and improve your skillset with any of the tutorials below.