CODEWITHSUNDEEP
sqlsql-serverunicode
senshin
senshin

Reputation: 10360

How do I get LIKE not to match accented characters?

I'm trying to write a query on SEDE that looks for certain accented characters in the titles of posts - something like this:

SELECT
  b.Id AS [Post Link]
FROM
  Posts b
WHERE
  b.Title LIKE '%[ĀāĒēĪīŌōŪūÂâÊêÎîÔôÛû]%'

This, unfortunately, returns any post whose title contains a vowel at all, accented or not, as we can see by running:

SELECT
  CASE
    WHEN 'tuv' LIKE '%[ĀāĒēĪīŌōŪūÂâÊêÎîÔôÛû]%'
    THEN 'matched'
    ELSE 'not matched'
  END

which results in 'matched' being returned.

What should I do to perform a query that selects only those rows for which Title contains one of the accented characters I've specified? From my vague understanding of SQL, I've been led to believe that this might entail using COLLATE somehow. (Note that I don't have control over the database in this case, so I can't change any settings at the table or database level or anything - I have to work with what SEDE already supplies.)

Upvotes: 1

Views: 900

Answers (1)

M.Ali
M.Ali

Reputation: 69594

The only thing you are missing is the N prefix. When working with unicode characters you always need to tell sql server explicitly that strings can contain some unicode character by prefixing the string with N

SELECT
  b.Id AS [Post Link]
FROM
  Posts b
WHERE
  b.Title LIKE N'%[ĀāĒēĪīŌōŪūÂâÊêÎîÔôÛû]%'

Same is true when you are inserting, Updating data 

INSERT INTO TableName(ColumName)
VALUES (N'ĀāĒēĪīŌōŪūÂâÊêÎîÔô')

Upvotes: 4

Related Questions