SQL query that distinguishes between ß and ss

Question

It seems that ß=ss in SQL. I need to be able to distinguish on a strict charcter equivalent basis.

i.e. SELECT * from table WHERE name LIKE '%ß%'

yields

Brian Bruß Steven Sossmix

etc..

I've looked at different Collations to see if there is one that ignores character expansions, but no luck so far.

Database has Latin1_General_CI_AS as default - I've also tried Latin1_General_CS_AS and Latin1_General_CS_AS_WS, neither of which worked.

Any help would be appreciated.

Which database server are you using? SQL Server, Oracle, MySQL or...? — Michael, Apr 21 '09 at 14:10
And what are the localization settings - which code set and collation is in force by default. — Jonathan Leffler, Apr 21 '09 at 14:14
I get your results when I prefix ss with N. -> N'ss' otherwise it works as expected (for me). — jason saldo, Apr 21 '09 at 14:19

score 11 · Accepted Answer · answered Apr 21 '09 at 14:29

11

You need to use a binary collation so that it's case-sensitive, accent-sensitive, etc.

Collation: SQL_Latin1_General_Cp437_BIN

answered Apr 21 '09 at 14:29

Jose Basilio

score 1 · Answer 2 · answered Apr 21 '09 at 14:21

1

Found a thread that has two solutions for this.

answered Apr 21 '09 at 14:21

TStamper

One answer was from Jon Skeet, so maybe he could step in and elaborate on it – TStamper Apr 21 '09 at 14:23

2 Answers2