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
Advertisement
Answer
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);