[FIXED] why does SQL server consider SS as an Umlaut-S (ß)?

Issue

When I run the below query:

select charindex('ß','COMMISSIONING')

it returns a 6.

I specifically chose ß for a function as I thought it very unlikely to appear in any of my data. This was working perfectly fine for several weeks but now all of the sudden it's detecting ß where there is no ß and therefore screwing up my function.

Will someone please tell me what's caused this?

I'm using SQL server 2016

Solution

Because, as discussed in the comments, SS is an "uppercase" ß. If you don't want the characters to match you need your collation to be case sensitive. For the below, the value returned for both CHARINDEX expressions is 0:

SELECT C, charindex('ß',V.C)
FROM (VALUES('COMMISSIONING' COLLATE Latin1_General_CS_AS)) V(C);

SELECT C, charindex('ß',V.C)
FROM (VALUES('COMMISSIONING' COLLATE Latin1_General_CS_AI)) V(C);

Answered By - Larnu

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)