I am going to continue my series of very short articles or tidbits on Transaction SQL string functions. I will exploring the DIFFERENCE() function today.
The DIFFERENCE() function takes two string arguments and computes how different the two strings sound when spoken. A return value of 4 means the strings sound the same while a value of 0 means the strings sound totally different.
When using the difference function, you should look at the SOUNDEX() function. This function calculates a four-character code that is based on how the string sounds when spoken.
The following two examples shows four different words representing animals. One pair is very simular and one pair is very different.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
-- Example returns 4, words are very close select soundex('Dog') as word_val1, soundex('Dogs') as word_val2, difference('Dog', 'Dogs') as how_close -- Example returns 0, words are very different select soundex('Rattle-Snake') as word_val1, soundex('Mongoose') as word_val2, difference('Rattle-Snake', 'Mongoose') as how_close |
|
1 2 3 4 5 6 7 8 9 10 |
output: word_val1 word_val2 how_close --------- --------- ----------- D200 D200 4 word_val1 word_val2 how_close --------- --------- ----------- R340 M522 0 |
One thing to always worry about when using TSQL functions is how will it react with a UNKNOWN or NULL values?
|
1 2 3 4 |
-- Anything compared to NULL results in NULL select difference(NULL, 'Mongoose') as how_close; select difference('Mongoose', NULL) as how_close; select difference(NULL, NULL) as how_close; |
|
1 2 3 4 5 |
output: how_close ----------- NULL |
Next time, I will be exploring the LEN() function.
