Querying for ae matches æ in SQL Server / LINQ to SQL

Question

When I try running...

SELECT * FROM Users WHERE Username = 'ae' it returns matches where the username is æ (ash character).

I was wondering if there's a way to support characters like the ash character but get an exact match for what I'm looking for.

I'd like to search for ae and get just ae, not ae and æ

I'm using SQL Server 2008 and SQL Server 2008 R2.

Answer 1

You could use an ordinal collation for your comparison. For example:

SELECT *
FROM Users
WHERE Username = 'ae' COLLATE Latin1_General_BIN

Answer 2

This doesn't seem to happen with SQL collations

;with Users As
(
select 'æ' as Username UNION ALL SELECT 'ae'
)
SELECT *
FROM Users WHERE Username = 'ae' collate SQL_Latin1_General_CP1_CI_AS