Querying A Sql Server 2008 Table To Find Values In A Column Containing Unicode Characters

I've run into a problem in a project I'm working on: some of the string values in a specific SQL Server 2008 table column contain Unicode characters. For example, instead of a das

Solution 1:

You want to find all strings that contain one or more characters outside ASCII characters 32-126.

I think this should do the job.

SELECT*FROM your_table
WHERE your_column LIKE N'%[^ -~]%'collate Latin1_General_BIN

Solution 2:

One way you can do it is to see which rows no longer equal themselves when converted to a datatype that doesn't support unicode.

CREATETABLE myStrings (
    string nvarchar(max) notnull
)

INSERTINTO myStrings (string)
SELECT'This is not unicode'unionallSELECT'This has '+nchar(500)+' unicode'unionallSELECT'This also does not have unicode'unionallSELECT'This has lots of unicode '+nchar(600)+nchar(700)+nchar(800)+'!'SELECTcast(string asvarchar)
FROM myStrings

SELECT*FROM myStrings
WHEREcast(cast(string asvarchar(max)) as nvarchar(max)) <> string

Solution 3:

SELECT*FROM your_table
WHERE your_column LIKE N'%[^ -~]%'collate Latin1_General_BIN

finds all strings that contain one or more characters within ASCII characters 32-126.

I thought the purpose was to find strings where ASCII characters are not in the range 32-126?

NOT is possible with LIKE. Wouldn't this work?

SELECT*FROM your_table
WHERE your_column NOTLIKE N'%[^ -~]%'

No collate required.