SQL Function DIFFERENCE returns interesting scores
If you change the order of words you will see there is a bias on the first word. Then if you consider SOUNDEX you will begin to understand why. Also read the reference below.

SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken in English. The
first character of the code is the first character of
character_expression, converted to upper case. The second through
fourth characters of the code are numbers that represent the letters
in the expression. The letters A, E, I, O, U, H, W, and Y are ignored
unless they are the first letter of the string. Zeroes are added at
the end if necessary to produce a four-character code. For more
information about the SOUNDEX code, see The Soundex Indexing
System

-- Bias based on left to right order of words
SELECT 10 id, DIFFERENCE('Good', 'Good Samaritans') --Result is 4 (High score match)
union all
SELECT 11 id, DIFFERENCE('Good', 'Samaritans Good') --Result is 1 (Low score match)
union all
SELECT 20, DIFFERENCE('Samaritans', 'Good Samaritans') --Result is 1 (Low score match)
union all
SELECT 21, DIFFERENCE('Samaritans', 'Samaritans Good') --Result is 4 (High score match)
union all
SELECT 30, DIFFERENCE('Sam', 'Good Samaritans') --Result is 2 (On the upper low side)






id
(No column name)




10
4


11
1


20
1


21
4


30
2




fiddle
Your expectations from difference may be too high.

Can anyone explain why the SQL function (using SQL Server 2019) returns results that to me appear to be counter intuitive? Here are the queries and the scores:
SELECT 
    DIFFERENCE('Good', 'Good Samaritans'); --Result is 4 (High score match)

SELECT 
    DIFFERENCE('Samaritans', 'Good Samaritans'); --Result is 1 (Low score match)

SELECT 
    DIFFERENCE('Sam', 'Good Samaritans'); --Result is 2 (A higher score than above!)

I understand DIFFERENCE uses SOUNDEX to match consonants phonetically, but the results above seem very odd particularly with the second query. Is it something to do with the space and the proceeding string?
