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.

   <source lang="sql">

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)</source>

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

   <source lang="sql">

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)</source>

DIFFERENCE("Nite", "Knight")

   <source lang="sql">

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></source>

SELECT DIFFERENCE ("Redmond", "Renton")

   <source lang="sql">

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

---

         3

(1 rows affected)</source>

Use DIFFERENCE in where clause

   <source lang="sql">

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</source>

Use SOUNDEX(au_lname) and DIFFERENCE

   <source lang="sql">

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></source>

Using String Functions to Search for Phonetically Similar Data

   <source lang="sql">

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)</source>