3

I followed this post How do I perform an accent insensitive compare (e with è, é, ê and ë) in SQL Server? but it doesn't help me with " ş ", " ţ " characters.

This doesn't return anything if the city name is " iaşi " :

SELECT *
  FROM City
 WHERE Name COLLATE Latin1_general_CI_AI LIKE '%iasi%' COLLATE Latin1_general_CI_AI

This also doesn't return anything if the city name is " iaşi " (notice the foreign ş in the LIKE pattern):

SELECT *
  FROM City
 WHERE Name COLLATE Latin1_general_CI_AI LIKE '%iaşi%' COLLATE Latin1_general_CI_AI

I'm using SQL Server Management Studio 2012.
My database and column collation is "Latin1_General_CI_AI", column type is nvarchar.

How can I make it work?

Community
  • 1
  • 1
alex
  • 1,300
  • 1
  • 29
  • 64

5 Answers5

2

The characters you've specified aren't part of the Latin1 codepage, so they can't ever be compared in any other way than ordinal in Latin1_General_CI_AI. In fact, I assume that they don't really work at all in the given collation.

If you're only using one collation, simply use the correct collation (for example, if your data is turkish, use Turkish_CI_AI). If your data is from many different languages, you have to use unicode, and the proper collation.

However, there's an additional issue. In languages like Romanian or Turkish, ş is not an accented s, but rather a completely separate character - see http://collation-charts.org/mssql/mssql.0418.1250.Romanian_CI_AI.html. Contrast with eg. š which is an accented form of s.

If you really need ş to equal s, you have replace the original character manually.

Also, when you're using unicode columns (nvarchar and the bunch), make sure you're also using unicode literals, ie. use N'%iasi%' rather than '%iasi%'.

Luaan
  • 62,244
  • 7
  • 97
  • 116
  • "... you have (to) replace the original character manually", are you sure that's the only solution? is this issue related only to MSSQL? what about MySQL? – superware Mar 05 '16 at 21:14
  • @superware Well, if MySQL follows the Unicode standard properly, it has the same problem. The two letters have nothing in common, as far as the script is concerned. Sure, they kind of look similar, but they aren't actually related. Sure, some people may use the similar character when they want to write using an english keyboard, but that doesn't really matter for script. – Luaan Mar 06 '16 at 08:22
2

In SQL Server 2008 collations versioned 100 were introduced.

Collation Latin1_General_100_CI_AI seems to do what you want.

The following should work:

SELECT * FROM City WHERE Name LIKE '%iasi%' COLLATE Latin1_General_100_CI_AI
Catalin Antohi
  • 21
  • 2
1

Not tidiest solution I guess, but if you know that it's just the "ş" and "ţ" characters that are the problem, would it be acceptable to do a replace?

SELECT *
FROM City
WHERE replace(replace(Name,'ş','s'),'ţ','t') LIKE COLLATE Latin1_general_CI_AI '%iasi%' COLLATE Latin1_general_CI_AI
ScubaManDan
  • 809
  • 8
  • 22
  • it doesn't return anything, even if I search for " iaşi " – alex Mar 14 '14 at 16:15
  • If you're going to have the special characters in your search term as well, you'll need the replace in your search term as well. Though might as well just leave is as 'iasi' rather than 'iaşi'. I think what the others are suggesting should be your first point of call though. – ScubaManDan Mar 16 '14 at 12:48
0

You just need to change collation of name field before like operation. Check test code below

DECLARE @city TABLE ( NAME NVARCHAR(20) )

INSERT INTO @city
    VALUES ( N'iaşi' )

SELECT *
    FROM @city
    WHERE name LIKE 'iasi'
--No return

SELECT *
    FROM @city
    WHERE name COLLATE Latin1_general_CI_AI LIKE '%iasi%'
--Return 1 row
0

This problem was haunting me for some time, until now, when I've finally figured it out. Presuming your table or column is of SQL_Latin1_General_CP1_CI_AS collation, if you do:

update 
    set myCol = replace(myCol , N'ș', N's')
from MyTable

and

update 
    set myCol = replace(myCol,N'ț',N't')
from MyTable

the replace function will not find these characters, because the "ș" made from your keyboard (Romanian Standard keyboard) differs from the "ş" or "ţ" found in your database. As a comparison: ţț and şș - you can see that they differ because the accents are closer to the "s" or "t" character.

Instead, you must do:

update 
    set myCol = replace(myCol , N'ş', N's')
from MyTable

and

update 
    set myCol = replace(myCol,N'ţ',N't')
from MyTable
Daniel Cristian
  • 49
  • 4