Select statement problem

Question

Hello

I have a db with a nvarchar column "name" (collation SQL_Latin1_General_CP1_CI_AS) with values in several languages.

Running a query "Select * From Customers Where name like '%Γ%'"  (Γ is greek letter) I do not get the results I would expect but rows that satisfies the condition '%G%' - looks like "Γ" is "translated to "G".

I added "Collate Greek_CI_AS" but still did not work - although even if it work it still not satisfatory since depending on the filter I should add the appropriate collation!

Any ideas?

Yannis

---

Yannis Makarounis

Answer 1

This is because '%Γ%' is a varchar constant and thus interpreted according to the database collation, which apparently is SQL_Latin_General1_CP1_CI_AS. This collation uses code page 1252 for varchar, and this code page does not include gamma. Instead the gamma is converted to a fallback character, which is G in this case. You need to use put an N in front to make it an nvarchar literal: N'%Γ%'

Here is an example that illustrates:

CREATE TABLE mytable (latincol nvarchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                    greekcol nvarchar(20) COLLATE Greek_CI_AS NOT NULL)
go
INSERT mytable (latincol, greekcol)
   VALUES (N'Gremlin', N'Gremlin')
INSERT mytable (latincol, greekcol)
   VALUES (N'Digging', N'Digging')
INSERT mytable (latincol, greekcol)
   VALUES (N'Γάμμα', N'Γάμμα')
INSERT mytable (latincol, greekcol)
   VALUES (N'Ωμεγα', N'Ωμεγα')
go
SELECT * FROM mytable WHERE latincol LIKE  '%Γ%' 
SELECT * FROM mytable WHERE greekcol LIKE  '%Γ%' 
SELECT * FROM mytable WHERE latincol LIKE  N'%Γ%' 
SELECT * FROM mytable WHERE greekcol LIKE  N'%Γ%' 
go
DROP TABLE mytable

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Hello

It works. Actually I thought I tried that and it didn't work but I might have type the 'N' in front in greek as well!

Thank you.

Y

---

Yannis Makarounis