SQL LIKE with special characters

Question

If I try to create a select with special (nordic) characters like:

Select * from users where name like '%æ%'

It just selects all users instead of those containing the letter 'æ'.

Do I need to install some special drivers to the database, or is there something else I have missed?

Update:
I am using a SQL Server 2008 database, the collation is set to 'SQL_Latin1_General_CP1_CI_AS' and the datafield is a nullable nVarChar datatype.

Answer 1

Most likely some collation or datatype issue

Example, gives 97 and 230

SELECT ASCII('æ' COLLATE Albanian_CI_AI), ASCII('æ' COLLATE Latin1_General_CI_AS) 

We'll need more info basically.

Edit: Question about Danish/Norwegian å (although unresolved)

Edit 2: change the code to this if name is nvarchar so the literal becomes unicode too.

Select * from users where name like N'%æ%'