SQL Server - Searching string with international characters using LIKE clause

Question

I have a field 'Description' which can have product descriptions with any unicode characters. If I search for a description which contains an international character, with a LIKE condition (word searched with does not have the international character) I get the following results:

Ex: GEWÜRZTRAMINER is one of the descriptions. When I do:

Select * from table where Description LIKE '%GEWURZTRAMINER%', it retrieves the entry.

When I do:

Select * from table where Description LIKE '%GEWURZ%', the entry is not retrieved.

(Note: the search condition does not include the Ü but has a U)

Is there a way around this so that I can retrieve with '%GEWURZ%' as well?

Answer 1

For bog standard varchar, you'd have to coerce to a accent insensitive collation

Select 1 where 'GEWÜRZTRAMINER' COLLATE LATIN1_GENERAL_CI_AI LIKE '%GEWURZTRAMINER%'

There should be no difference between the calls though for the SQL you provided.

Answer 2

It will depend on the collation order for the column. It should work if you use e.g. SQL_Latin1_General_CP1_CI_AI