Why is SQL Server's SOUNDEX giving me inconsistent results?

Question

Can someone provide some insight on why the SOUNDEX is giving different results when certain characters are capitalized vs lower case? From what I read online, SOUNDEX ignores case but I am getting different results and can't wrap my head around what would cause the differences. I can't mimic this example with are word like GUTTHRE.

Query                                  Result
select SOUNDEX('JESCHKE')              J200      <-- 200
select SOUNDEX(LOWER('JESCHKE'))       J220
select SOUNDEX('Jeschke')              J220
select SOUNDEX('jeschke')              J220
select SOUNDEX('JESChKE')              J220
select SOUNDEX('JESCHke')              J200      <-- for some reason capitalizing 'H' changes the result to 200
select SOUNDEX('jescHke')              J200      <-- 200

Any ideas why the values don't match?

_[SOUNDEX](https://learn.microsoft.com/en-us/sql/t-sql/functions/soundex-transact-sql) is collation sensitive._ But the docs don't describe exactly how collation affects the result. — AlwaysLearning, Aug 20 '20 at 22:20
@AlwaysLearning so it looks like case could be part of the collation sensitivity.. when I read that I was only considering order and accents. — CandleWax, Aug 20 '20 at 22:31
The reference for the soundex rules strongly suggests that the case of the characters does not matter: https://www.archives.gov/research/census/soundex. — Gordon Linoff, Aug 20 '20 at 23:11

score 1 · Accepted Answer · answered Aug 21 '20 at 00:08

So wiki states:

"two letters with the same number separated by 'h' or 'w' are coded as a single number"

So I think this applies when you are using a lowercase "h" hence the S is coded as a 2 and all the other characters would encode to 2 and are ignored as per:

If two or more letters with the same number are adjacent in the original name (before step 1), only retain the first letter

I suspect that because the H is capitalised it resets this rule so that the following k is then coded as another 2.

Why is SQL Server's SOUNDEX giving me inconsistent results?

1 Answers1