Saturday, February 09, 2013 10:10 AM
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!
- Changed Type Kalman TothMicrosoft Community Contributor, Moderator Saturday, February 09, 2013 1:06 PM question
Saturday, February 09, 2013 11:15 AM
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, firstname.lastname@example.org
- Marked As Answer by YannisMakarounis Saturday, February 09, 2013 1:43 PM
Saturday, February 09, 2013 1:46 PM
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!