Search code examples
sqlsql-serversql-likesql-server-2014-express

Microsoft SQL Server does not return everything when using LIKE clause

I have a Products table in SQL Server 2014 Express with records in it.

A few product names (records) are the following:

  • Test product
  • Teszt termék
  • Teszt termék 2

When I execute the following query, everything works just fine:

SELECT * 
FROM Products
WHERE name LIKE 'te%'

It retrieves all three records. However, when I use

SELECT * 
FROM Products
WHERE name LIKE 'tes%'

is executed, only "Test product" is retrieved. And when the query is

SELECT * 
FROM Products
WHERE name LIKE 'tesz%'

then it works again, it fetches all the records starting with "Tesz".

Any idea what the problem might be?

Thanks for your help in advance.

Solution

  • I downloaded your backup.

    The database collation is set to Hungarian_CI_AS.

    Sz is treated as a letter in its own right in Hungarian (the thirty-second letter of the Hungarian alphabet). If you do not want this behaviour then you need to avoid Hungarian collations and it should work. (I tested all SQL Server 2014 collations with names ending CI_AS and the only ones showing the behaviour are Hungarian_CI_AS, Hungarian_100_CI_AS, SQL_Hungarian_CP1250_CI_AS)

    You can do this either by altering the column collation (preferable) or by adding an explicit COLLATE clause to the query (less efficient and will block queries such as WHERE name LIKE 'tes%' being resolved through an index seek).

    You might also consider changing the database collation but this will not cascade through to pre-existing table columns created on the old collation.