String compare with 'strange chars' in SqlServer

Question

I have some content in our database with (for our language) 'strange' characters.

For example:

Å

When using the like statement in Sql Server with the letter A, this does not return a result because the Å is not an A.

Is there a way in SqlServer to treat the Å as an E (and the é as an 'e' etc)?

Answer 1

Do you mean "Å as A" or "Å as E"?

For the first, you can coerce the collation to accent insensitive

SELECT 1 WHERE 'Å' = 'A'    --No
SELECT 1 WHERE 'Å' COLLATE LATIN1_General_CI_AI = 'A'   --Yes

SELECT 1 WHERE 'é' = 'e'    --No
SELECT 1 WHERE 'é' COLLATE LATIN1_General_CI_AI = 'e'   --Yes

Answer 2

You can include a COLLATE clause in your query to specify an accent-insensitive collation:

SELECT *
FROM your_table
WHERE your_column LIKE 'A%' COLLATE Latin1_General_CI_AI

You'll need to use an appropriate collation for your language. The built-in fn_helpcollations function will give you a list of all supported collations:

SELECT *
FROM fn_helpcollations()