0

I have a table as following

Create Table Test(Name NVARCHAR(100))

Insert Into Test(Name) values (N'ἀἁἃἄὠἀἁἃἄὠἀἁἃἄὠἀἁἃἄὠἀἁἃἄὠἀἁἃἄὠἀἁἃἄὠ1,')
Insert Into Test(Name) values (N'ἀἁἃἄὠἀἁἃἄ')
Insert Into Test(Name) values (N'Test')

Select * from ED.Test WHERE Name like N'ἀἁἃἄὠἀἁἃἄ'

This above Select query should return two results but now it is returning only one result.

This statement 

Select * from ED.Test WHERE Name like '%ἀἁἃἄὠἀἁἃἄ%'

not returning any result.

Select * from ED.Test WHERE Name like N'%ἀἁἃἄὠἀἁἃἄ%'

returning all result.

What am I missing?

Robert
  • 25,425
  • 8
  • 67
  • 81
Puneet Kumar
  • 11
  • 2

1 Answers1

2

For special languages characters you should use COLLATE.

Select * from Test 
WHERE Name  COLLATE Greek_100_CS_AS_KS_WS_SC  like N'ἀἁἃἄὠἀἁἃἄ%'

Sql Fiddle Demo

For your solution I used collation_name Greek_100_CS_AS_KS_WS_SC

Below query returns all possible collation_name

SELECT *
FROM ::fn_helpcollations()

You can chose which collation_name suits you.

Robert
  • 25,425
  • 8
  • 67
  • 81
  • It didn't helped returning all result. – Puneet Kumar May 08 '15 at 09:12
  • @PuneetKumar It return 2 rows. What's wrong with it? – Robert May 08 '15 at 09:16
  • I am not sure whats wrong , I am using SQL server 2008 R2 enterprise edition. – Puneet Kumar May 08 '15 at 09:54
  • @PuneetKumar My query return row with `Test`? – Robert May 08 '15 at 09:56
  • I want to select only two row with having 'ἀἁἃἄὠἀἁἃἄ' char set . – Puneet Kumar May 08 '15 at 09:57
  • From Like statement it look like simple Adding N clause and then use Like statement syntax but somehow it is not working. can you try to write a query that return only two row matching 'ἀἁἃἄὠἀἁἃἄ' char set. – Puneet Kumar May 08 '15 at 09:59
  • @PuneetKumar Yes, I know. In your example there are 2 rows with `ἀἁἃἄὠἀἁἃἄ` and one row wiht `TEST`. Does my query return row `test`? – Robert May 08 '15 at 09:59
  • Hi But i am having SQL_Latin1_General_CP1_CI_AI collation not Greek_100_CS_AS_KS_WS_SC. SELECT * FROM ED.Test WHERE CHARINDEX(N'ἀἁἃἄὠἀἁἃἄ',Name) > 0 this will work but can we assume Like operator is not working as wild card search on special characters – Puneet Kumar May 08 '15 at 10:24
  • 1
    @PuneetKumar - If you have language specific characters you need to compare them with appropriate collation when using `LIKE` as Parado mentioned. – ughai May 08 '15 at 10:27
  • @PuneetKumar It's not possible for this special characters – Robert May 08 '15 at 10:31
  • 1
    Even `CHARINDEX` will give you incorrect results without collation `SELECT * FROM Test WHERE CHARINDEX(N'ἀἁἃἄὠἀἁἃἄ',Name) > 0 SELECT * FROM Test WHERE CHARINDEX(N'1ὠ',Name) > 0 SELECT * FROM Test WHERE CHARINDEX(N'1ὠ',Name COLLATE Greek_100_CS_AS_KS_WS_SC) > 0` – ughai May 08 '15 at 10:32
  • Yes , Using Greek_100_CS_AS_KS_WS collation i get the result but the issue is in my existing query i am already using COLLATE SQL_Latin1_General_CP1_CI_AI like ('%'+@Name+'%') ESCAPE '\' , how it is possible to amend query in such a way that it won't break the existing flow. in above query wild card is not working and hence i am getting all result instead of filtered result. – Puneet Kumar May 08 '15 at 10:42
  • @Parodo : Thanks for your great help , but here issue is i am already using SQL_Latin1_General_CP1_CI_AI .Select * from Test WHERE Name COLLATE Greek_100_CS_AS_KS_WS_SC like +'%'+@name+'%' OR Name COLLATE SQL_Latin1_General_CP1_CI_AI like ('%'+@name+'%') , here it always give me three result because of COLLATE SQL_Latin1_General_CP1_CI_AI that i don't wanna remove because it is getting used on production. – Puneet Kumar May 08 '15 at 12:04
  • @PuneetKumar If you can't change the query - I have no idea how to help you :(. – Robert May 08 '15 at 12:09
  • @Parado: I got another solution like use full text search on the column where need to search special characters. – Puneet Kumar May 09 '15 at 19:41
  • @PuneetKumar I know there is `full text search` but I've never used it on production. It reliquaries and index on column, doesn't it? If that solution works for you can put it in an answer. I'm very curios how it works :) – Robert May 09 '15 at 19:49