How to identify non ascii characters in database column

[jasonhuibers]

In my table Table1 , there is a column in which we need to identify all non ascii character? We can run the query and provide the list of records in which there is a non ascii character and the data can be cleaned up the by customer. How can I do this in a query?

 

[drlex]

I'd use the ASCII function in conjunction with SUBSTRING and check every row. Numbers table joined on LEN(column_to_check) to step through each character.

soi là, soi carré

 

[gmmastros]

I suspect that you need to better qualify what you consider to be an ASCII character. By definition, anything in a varchar column is ASCII. I suspect you want to actually check for certain characters that are not within a certain set of acceptable characters. For example, a list of acceptable characters might be ({space},.-$a (through) z, (0-9), etc....).

I would encourage you to take a look at this query. It probably won't be exactly what you are looking for, but it may be a good place to start.

Select ColumnName 
From   TableName 
Where  ColumnName like '%[^a-z ,-.'0-9]%'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

What is the data type of the column? I suspect you may try playing with this idea

select * from myColumn where mynvarcharColumn <> convert(varchar(max), myNvarcharColumn) -- this should find all records with non-ASCII chars.

PluralSight Learning Library