CODEWITHSUNDEEP
sql-server-2008t-sqlsql-server-2008-r2string-comparison
Narutokk
Narutokk

Reputation: 1034

Two strings compare equal using '=' but fail in 'like' compare

Sql-Server 2008 R2 Collation is Chinese_Simplified_Pinyin_100_CI_AS. When I use 

select 1 where N'⑦' = N'7'

it output 1, but when I change the operator to like

select 1 where N'⑦' like N'7'

it wont output anything.

Why is like operator act so weird? Did I miss something?

Upvotes: 5

Views: 1143

Answers (1)

ErikE
ErikE

Reputation: 50201

It appears to be a bug. LIKE against a pattern without wildcards should always return the same thing that = returns.

Others can see this behavior by running the following query:

SELECT
   CASE WHEN N'⑦' COLLATE Chinese_Simplified_Pinyin_100_CI_AS = N'7' THEN 'Y' ELSE 'N' END,
   CASE WHEN N'⑦' COLLATE Chinese_Simplified_Pinyin_100_CI_AS LIKE N'7' THEN 'Y' ELSE 'N' END
-- Y N

I see that you reported it on Microsoft Connect.

Upvotes: 1

Related Questions