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

Question

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?

Answer 1

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.