Follow @endyourif rss Facebook LinkedIn

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

Published on Jun 10, 2022

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.

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'

Tags: SQL

My Books
ASP.NET MVC 5 With Bootstrap and Knockout.js
Knockout.js Building Dynamic Client-Side Applications
20 Recipes for Programming MVC 3
20 Recipes for Programming PhoneGap
Rapid Application Development with CakePHP