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.

[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)
[/code]

The example above usess nvarchar but can be easily adapted to varchar as well by changing this line:
c.DATA_TYPE = ‘nvarchar’