CODEWITHSUNDEEP
sqlsql-serverstringt-sqlsql-server-2008
Grant Smith
Grant Smith

Reputation: 765

Flatten national characters in SQL Server

I have a column that contains pet names with national characters. How do I write the query to match them all in one condition?

|PetName|

   Ćin
   ćin
   Ĉin
   ĉin
   Ċin
   ċin
   Čin
   čin

sth like FLATTEN funciton here:

...WHERE LOWER(FLATTEN(PetName)) = 'cin'

Tried to cast it to from NVARCHAR to VARCHAR but it didn't help. I'd like to avoid using REPLACE for every character.

Upvotes: 1

Views: 1157

Answers (3)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

There isn't really a way or built-in function that will strip accents from characters. If you are doing comparisons (LIKE, IN, PATINDEX etc), you can just force COLLATE if the column/db is not already accent insensitive.

Normally, a query like this

with test(col) as (
select 'Ćin' union all
select 'ćin')
select * from test
where col='cin'

will return both columns, since the default collation (unless you change it) is insensitive. This won't work for FULLTEXT indexes though.

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300559

You can change the collation used for the comparison:

WHERE PetName COLLATE Cyrillic_General_CI_AI = 'cin'

Upvotes: 2

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

this should work because cyrillic collation base cases all diacritics like Đ,Ž,Ć,Č,Š,etc...

declare @t table(PetName nvarchar(100))
insert into @t
SELECT N'Ćin' union all 
SELECT N'ćin' union all 
SELECT N'Ĉin' union all 
SELECT N'ĉin' union all 
SELECT N'Ċin' union all 
SELECT N'ċin' union all 
SELECT N'Čin' union all 
SELECT N'čin'

SELECT  *
FROM    @t
WHERE   lower(PetName) = 'cin' COLLATE Cyrillic_General_CS_AI

Upvotes: 6

Related Questions