SQL Difference()函数
SQL服务器的DIFFERENCE()函数用于比较两个字符串的SOUNDEX值。它接受两个参数exp1和exp2,并返回一个整数值,表示两个SOUNDEX值的匹配程度,范围从0到4。
SOUNDEX值是一个四个字符的代码,它基于字符串在英语中的发音。在这里,0值表示SOUNDEX值之间几乎没有相似性,而值为4表示较强的相似性或匹配的SOUNDEX值。
语法
下面是SQL Difference()函数的语法:
DIFFERENCE(expression, expression)
参数
此方法接受两个参数,都是字符数据的字母数字表示,可以是常量、变量或列。
返回值
它返回一个整数值,用于测量两个不同表达式(字符串)的SOUNDEX()值之间的差异。
示例
以下是使用DIFFERENCE()函数和相似的SOUNDEX值的示例-
SELECT SOUNDEX('Had') AS soundex_Had, SOUNDEX('Hadi') AS soundex_Hadi, DIFFERENCE('Had', 'Hadi') AS similarity;
输出
执行以上语句会产生以下输出结果:
+--------------+-----------------+-------------+
| soundex_Had | soundex_Hadi | similarity |
+--------------+-----------------+-------------+
| H300 | H300 | 4 |
+--------------+-----------------+-------------+
示例
以下是一个示例,返回两个表达式之间的最小差值3-
SELECT SOUNDEX('cool') AS soundex_cool, SOUNDEX('pool') AS soundex_pool,
DIFFERENCE('cool', 'pool') AS similarity;
输出
执行上述语句将产生以下输出 –
+---------------+-----------------+------------+
| soundex_cool | soundex_pool | similarity |
+---------------+-----------------+------------+
| C400 | P400 | 3 |
+---------------+-----------------+------------+
示例
以下是一个示例,返回一个差异值为2,其差异介于两个表达式之间 –
SELECT SOUNDEX('Jam') AS soundex_Jam, SOUNDEX('kisan') AS soundex_kisan,
DIFFERENCE('Jam', 'kisan') AS similarity;
输出
在执行上述语句时,会产生以下输出−
+--------------+-----------------+-------------+
| soundex_Jam | soundex_kisan | similarity |
+--------------+-----------------+-------------+
| J500 | k250 | 2 |
+--------------+-----------------+-------------+
示例
以下是一个示例,返回两个表达式之间的差值为1,具有最大可能的差异值−
SELECT SOUNDEX('Javascript') AS soundex_Javascript, SOUNDEX('SQL') AS soundex_SQL,
DIFFERENCE('Javascript', 'SQL') AS similarity;
输出
在执行上述语句时,产生以下输出结果−
+---------------------+-----------------+-------------+
| soundex_Javascript | soundex_SQL | similarity |
+---------------------+-----------------+-------------+
| J126 | S400 | 1 |
+---------------------+-----------------+-------------+
示例
下面是一个示例,返回差值为0,具有两个表达式之间可能的最大差值 −
SELECT SOUNDEX('Javascript') AS soundex_Javascript, SOUNDEX('C++') AS soundex_SQL,
DIFFERENCE('Javascript', 'C++') AS similarity;
输出
执行上述语句会产生以下输出-
+---------------------+-----------------+-------------+
| soundex_Javascript | soundex_C++ | similarity |
+---------------------+-----------------+-------------+
| J126 | C000 | 0 |
+---------------------+-----------------+-------------+
示例
现在,我们通过将列名作为参数传递来尝试这个函数。首先,假设我们已经创建了一个名为CUSTOMERS的表,如下所示:
create table CUSTOMERS(
ID INT NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
LAST_NAME VARCHAR(15) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(25),
SALARY DECIMAL(10, 4), PRIMARY KEY(ID)
);
现在让我们使用INSERT语句将记录插入其中,如下所示:−
insert INTO CUSTOMERS VALUES(1, 'Ramesh','Ramesh', 32, 'Ahmedabad', 2000.00);
insert INTO CUSTOMERS VALUES(2, 'Gopal','Rampal', 25, 'Delhi', 1500.00);
insert INTO CUSTOMERS VALUES(3, 'kaushik','Sharma', 23, 'Kota', 2000.00);
insert INTO CUSTOMERS VALUES(4, 'Ravichandran','Ramachandran', 25, 'Chennai', 6500.00);
以下查询检索顾客表中名字和姓氏之间的差异 –
输出
+----+------------+--------------+------+
| ID | FIRST_NAME | LAST_NAME | DIFF |
+----+------------+--------------+------+
| 2 | Ramesh | Ramesh | 2 |
| 3 | Khilan | Rampal 2 |
| 3 | kaushik | Sharma | 1 |
| 5 | Chaitali | Ramachandran | 3 |
+----+------------+--------------+------+