How to query Azure SQL server with specific type of hyphen?-CodePudding

I have some garbage in my DB. I want to know how much of it I have. The correct data should be like this foo-bar. Unfortunately I also have foo－bar.

If I run both queries I get the same data.

select * from data
where field like '%－%';
select * from data
where field like '%-%';

result for both queries

foo－bar
foo-bar

How can I query only for the data that contains － and not -? Both azure web client and azure data studio seem to somehow convert this char as if there is only one. These are two different Unicode characters https://www.codetable.net/decimal/45 versus https://www.codetable.net/decimal/65293

CodePudding user response：

You have 2 issues. One is that Unicode literal strings need an N prefix to denote Unicode. The other is a binary collation cast is needed to compare in the code points rather than characters:

SELECT * FROM data
WHERE field LIKE N'%－%' COLLATE Latin1_General_BIN;

SELECT * FROM data
WHERE field LIKE N'%-%' COLLATE Latin1_General_BIN;