Don’t copy last name with Apostrophe from MS word 2010 to SQL Server 2008 R2

Today I use the following SQL command to see how many users have Apostrophe in the last name column. However, it only returns 1 row. I feel so surprised.

SELECT *

FROM from Table

where last_NM like '%''%'

After investigation, I notice one user’s last name has the special Apostrophe and that’s why the data is never returned.

This makes me think that someone used MS word 2010 , entered his last name and made a copy/paste to SQL Server 2008 R2.

Do you see the difference about Apostrophe ? The first one and the third one have the same Apostrophe .

If I use the following SQL query with special Apostrophe, the data is returned.

SELECT *

FROM from Table

where last_NM like '%’%'