в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:22:23Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/String Functions/DIFFERENCE - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия