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.



declare
@sql    varchar(max)    = ''
,@table sysname         = 'customer' -- enter your table here
;with ColumnData as (
select
RowId               = row_number() over (order by c.COLUMN_NAME)
,c.COLUMN_NAME
,ColumnName         = '[' + c.COLUMN_NAME + ']'
,TableName          = '[' + c.TABLE_SCHEMA + '].[' + c.TABLE_NAME + ']'
from
INFORMATION_SCHEMA.COLUMNS c
where
c.DATA_TYPE         = 'nvarchar'
and c.TABLE_NAME    = @table
)
select
@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)
from
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.

Jamie Munro
Jamie Munro

Jamie Munro is the author of ASP.NET MVC 5 with Bootstrap and Knockout.js, Knockout.js: Building Dynamic Client-Side Web Applications, 20 Recipes for Programming MVC 3, and 20 Recipes for Programming PhoneGap. He has been developing websites and web applications for over 20 years.

Jamie began his writing career in 2009. As the success of Jamie's blog grew, he turned his writing passion to books about web development in hopes that his many years of experience could be passed on to his readers.

Tutorials

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.

Front-End

Server-Side

Database