MS SQL 2008 question - Difference function

[brandonb]

Hi All,

I have a question.

SELECT DIFFERENCE('BREWER', 'BUTLER')
SELECT DIFFERENCE('BUTLER', 'BREWER')

Why do these return different results? Is it a bug?

 

[KIAman]

A short explanation is that 'Brewer' sounds more like 'Butler' than 'Butler' sounds like 'Brewer'

Edit: Also, this is not a bug.

 

[Cogman]

A short explanation is that 'Brewer' sounds more like 'Butler' than 'Butler' sounds like 'Brewer'

Edit: Also, this is not a bug.

? weird to think about. Do you know how it is calculating the numbers, I would be interested to know.

I can understand that mathematically 3 - 2 isn't the same as 2 - 3. I just don't see where this function is deriving its numbers from.

 

[KLin]

When I run the soundex function, I get B660 for Brewer, and B346 for Butler. It probably has to do with the repeating 6.

 

[KIAman]

I was writing up some pseudo-code but this is a human friendly explanation.

Soundex('Brewer') = B660 = A[]
Soundex('Butler') = B346 = B[]

SELECT DIFFERENCE('BREWER', 'BUTLER')
difference starts at 0

Iteration 1:
B[0] = A[0] /* difference is now 1 */

Iteration 2:
B[1] != A[1]
B[1] != A[2]
B[1] != A[3]

Iteration 3:
B[2] != A[1]
B[2] != A[2]
B[2] != A[3]

Iteration 4:
B[3] != A[1]
B[3] = A[2] /* difference is now 2 */
B[3] = A[3] /* difference is now 3 */