How to use 'LIKE' statement with unicode strings?

Question

I am trying to execute a query with unicode characters. I was able to execute the normal equality query by prepending N to the query (Eg: ..... WHERE column=N'exact_stringâ'). But that doesn't seem to work when I try to use LIKE. Any ideas on how to make this work?

Sample query: SELECT * FROM t_sample WHERE t_column LIKE N'%â%'

Also how can I know which encoding does the SQL Server use to store the nvarchar or nchar data type and what encoding it uses to show the query in SQL Editor?

EDIT: My bad. This actually works. I have tried executing the query in a wrong window. But the upside of this is that I learned about Collation settings in SQL Server.

Answer 1

Use a Unicode search string:

WHERE CONTRACTORNAME LIKE N'%ạ%'

Credit

Answer 2

Make sure the collation on your table supports unicode.