Querying a SQL Server 2008 table to find values in a column containing Unicode characters

Question

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 dash some strings will instead contain an EM DASH (http://www.fileformat.info/info/unicode/char/2014/index.htm).

The column values that contain Unicode characters are causing problems when I send HTTP requests to a third-party server. Is there a way to query what rows contain one-or-more Unicode characters, so I can at least begin to identify how many rows need to be fixed?

Well if your data type is unicode then all of the characters will be Unicode. Do you want to find all values which can't be round tripped to single byte in your default collation without data loss? — Martin Smith
– Martin Smith, Commented Mar 3, 2011 at 17:54
I think single-byte items may still include invisible control characters that I don't care about. It would be more accurate to say that I want to find all strings that contain one or more characters outside ASCII characters 32-126. — Mass Dot Net
– Mass Dot Net, Commented Mar 9, 2011 at 19:47

Martin Smith · Accepted Answer · 2011-03-09 20:30:45Z

18

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

edited Mar 9, 2011 at 20:30

answered Mar 9, 2011 at 20:11

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aaron Silverman · Accepted Answer · 2011-03-03 20:37:02Z

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.

CREATE TABLE myStrings (
    string nvarchar(max) not null
)

INSERT INTO myStrings (string)
SELECT 'This is not unicode' union all
SELECT 'This has '+nchar(500)+' unicode' union all
SELECT 'This also does not have unicode' union all
SELECT 'This has lots of unicode '+nchar(600)+nchar(700)+nchar(800)+'!'

SELECT cast(string as varchar)
FROM myStrings

SELECT *
FROM myStrings
WHERE cast(cast(string as varchar(max)) as nvarchar(max)) <> string

answered Mar 3, 2011 at 20:37

Aaron Silverman

22.7k21 gold badges89 silver badges106 bronze badges

1 Comment

Richard Over a year ago

what did your results look like? I tried something similar and it failed. I think it had something to do with DASH and HYPHEN

user2466387 · Accepted Answer · 2014-03-20 18:57:46Z

-1

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 NOT LIKE N'%[^ -~]%'

No collate required.

answered Mar 20, 2014 at 18:57

user2466387

591 silver badge5 bronze badges

Collectives™ on Stack Overflow

Querying a SQL Server 2008 table to find values in a column containing Unicode characters

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related