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:

...W开发者_如何学JAVAHERE 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.

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 

You can change the collation used for the comparison:

WHERE PetName COLLATE Cyrillic_General_CI_AI = 'cin' 

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.