SQL Server/T-SQL Tutorial/String Functions/DIFFERENCE

DIFFERENCE function returns a value between 0 and 4 that reflects how close a match there is between the two strings.

A value of 0 means that the two strings aren"t even close.
A value of 4 means a perfect match.

8> DECLARE @STRING_DIFFERENCE1 varchar(6)
9> DECLARE @STRING_DIFFERENCE2 varchar(6)
10> SET @STRING_DIFFERENCE1 = "Dewson"
11> SET @STRING_DIFFERENCE2 = "Joosun"
12> SELECT DIFFERENCE(@STRING_DIFFERENCE1,@STRING_DIFFERENCE2)
13> GO
-----------
          2
(1 rows affected)

DIFFERENCE() is a wrapper around two SOUNDEX().

It simplifies the comparison, reducing the result to a scale from 0 to 4, where the value 4 indicates a very close or exact match.
4>
5> SELECT DIFFERENCE("To", "Two")
6> GO
-----------
          4
(1 rows affected)

DIFFERENCE("Nite", "Knight")

5> SELECT
6> "SX_KNIGHT"=SOUNDEX("Knight"),
7> "SX_NITE"=SOUNDEX("Nite"),
8> "DIFFERENCE"=DIFFERENCE("Nite", "Knight")
9> GO
SX_KNIGHT SX_NITE DIFFERENCE
--------- ------- -----------
K523      N300              1
(1 rows affected)
1>

SELECT DIFFERENCE ("Redmond", "Renton")

1>
2> SELECT DIFFERENCE ("Redmond", "Renton")
3> GO
-----------
          3
(1 rows affected)

Use DIFFERENCE in where clause

4> CREATE TABLE authors(
5>    au_id          varchar(11),
6>    au_lname       varchar(40)       NOT NULL,
7>    au_fname       varchar(20)       NOT NULL,
8>    phone          char(12)          NOT NULL DEFAULT ("UNKNOWN"),
9>    address        varchar(40)           NULL,
10>    city           varchar(20)           NULL,
11>    state          char(2)               NULL,
12>    zip            char(5)               NULL,
13>    contract       bit               NOT NULL
14> )
15> GO
1> insert authors values("1",  "Joe",   "Abra",   "111 111-1111", "6 St.", "Berkeley",  "CA", "11111", 1)
2> insert authors values("2",  "Jack",  "Majo",   "222 222-2222", "3 St.", "Oakland" ,  "CA", "22222", 1)
3> insert authors values("3",  "Pink",  "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1)
4> insert authors values("4",  "Blue",  "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1)
5> insert authors values("5",  "Red",   "Anne",   "555 555-5555", "6 Av.", "Regina",    "SK", "55555", 1)
6> insert authors values("6",  "Black", "Michel", "666 666-6666", "3 Pl.", "Regina",    "SK", "66666", 1)
7> insert authors values("7",  "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1)
8> insert authors values("8",  "Yellow","Heather","888 888-8888", "3 Pu",  "Vacaville", "CA", "88888", 0)
9> insert authors values("9",  "Gold",  "Dep",    "999 999-9999", "5 Av.", "Oakland",   "CA", "99999", 0)
10> insert authors values("10", "Siler", "Dean",   "000 000-0000", "4 Av.", "Oakland",   "CA", "00000", 1)
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> SELECT au_lname,
4> Soundex=SOUNDEX(au_lname),
5> Diff_Larsen=DIFFERENCE(au_lname, "Larson")
6> FROM authors
7> WHERE
8> DIFFERENCE(au_lname, "Larson") >= 3
9> GO
au_lname                                 Soundex Diff_Larsen
---------------------------------------- ------- -----------
(0 rows affected)
1>
2> drop table authors;
3> GO

Use SOUNDEX(au_lname) and DIFFERENCE

3> CREATE TABLE authors(
4>    au_id          varchar(11),
5>    au_lname       varchar(40)       NOT NULL,
6>    au_fname       varchar(20)       NOT NULL,
7>    phone          char(12)          NOT NULL DEFAULT ("UNKNOWN"),
8>    address        varchar(40)           NULL,
9>    city           varchar(20)           NULL,
10>    state          char(2)               NULL,
11>    zip            char(5)               NULL,
12>    contract       bit               NOT NULL
13> )
14> GO
1> insert authors values("1",  "Joe",   "Abra",   "111 111-1111", "6 St.", "Berkeley",  "CA", "11111", 1)
2> insert authors values("2",  "Jack",  "Majo",   "222 222-2222", "3 St.", "Oakland" ,  "CA", "22222", 1)
3> insert authors values("3",  "Pink",  "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1)
4> insert authors values("4",  "Blue",  "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1)
5> insert authors values("5",  "Red",   "Anne",   "555 555-5555", "6 Av.", "Regina",    "SK", "55555", 1)
6> insert authors values("6",  "Black", "Michel", "666 666-6666", "3 Pl.", "Regina",    "SK", "66666", 1)
7> insert authors values("7",  "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1)
8> insert authors values("8",  "Yellow","Heather","888 888-8888", "3 Pu",  "Vacaville", "CA", "88888", 0)
9> insert authors values("9",  "Gold",  "Dep",    "999 999-9999", "5 Av.", "Oakland",   "CA", "99999", 0)
10> insert authors values("10", "Siler", "Dean",   "000 000-0000", "4 Av.", "Oakland",   "CA", "00000", 1)
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT au_lname,
3> Soundex=SOUNDEX(au_lname),
4> Diff_Smythe=DIFFERENCE(au_lname, "Smythe")
5> FROM authors
6> WHERE
7> DIFFERENCE(au_lname, "Smythe") >= 3
8> GO
au_lname                                 Soundex Diff_Smythe
---------------------------------------- ------- -----------
(0 rows affected)
1>
2> drop table authors;
3> GO
1>
2>

Using String Functions to Search for Phonetically Similar Data

Locating a customer name.
6>
7>
8> SELECT DIFFERENCE("Kathy Ward","kathy WARD"),
9>        DIFFERENCE("KATHY Ward","Cathy Ward"),
10>        DIFFERENCE("KATHY Ward","Cathie Ward")
11> GO
----------- ----------- -----------
          4           3           3
(1 rows affected)