16

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.

Aldwoni
  • 1,168
  • 10
  • 24
rkg
  • 5,559
  • 8
  • 37
  • 50
  • 2
    depends on your collation setting – Mitch Wheat Nov 23 '10 at 01:02
  • Thanks Mitch. Where can I check that setting for the current instance? – rkg Nov 23 '10 at 01:48
  • 2
    Your collation is per column, per table, per database (using inheritance unless otherwise specified). If you do a `sp_help` you should see what the collation on the columns is. –  Nov 23 '10 at 02:37

2 Answers2

29

Use a Unicode search string:

WHERE CONTRACTORNAME LIKE N'%ạ%'

Credit

Community
  • 1
  • 1
Shimmy Weitzhandler
  • 101,809
  • 122
  • 424
  • 632
3

Make sure the collation on your table supports unicode.

Brad
  • 15,361
  • 6
  • 36
  • 57
  • _ALL_ collations support Unicode in SQL Server. Unicode vs non-Unicode is simply a matter of datatype (`VARCHAR` = non-Unicode, except starting in SQL Server 2019 when it can now be Unicode via UTF-8, and `NVARCHAR` = Unicode via UTF-16). There are some collations that are Unicode-_only_ and have no 8-bit code page support (and thus do not work with `VARCHAR`), but everything works with `NVARCHAR`. – Solomon Rutzky Feb 13 '20 at 14:24