SQL - Difference() Function

---

---

The SQL server DIFFERENCE() function is used to compare two SOUNDEX values of the strings. It accepts two parameters, exp1 and exp2 and returns an integer value that indicates a match for two SOUNDEX values, from 0 to 4.

The Soundex value is a four-character code that is based on how the string sounds when spoken in English. Here, the 0 value indicates little or no similarity between SOUNDEX values, whereas the value 4 indicates strong similarity or matching SOUNDEX values.

Syntax

Following is the syntax of SQL Difference() Function −

DIFFERENCE(expression, expression)

Parameters

This method accepts two parameters both are alphanumeric representation of character data, which can be a constant, variable or column.

Return value

It returns an integer value measuring the difference between the SOUNDEX() values of two different expressions(strings).

Example

Following is an example using DIFFERENCE() function with similar SOUNDEX values −

SELECT SOUNDEX('Had') AS soundex_Had, SOUNDEX('Hadi') AS soundex_Hadi, DIFFERENCE('Had', 'Hadi') AS similarity;

Output

On executing the above statement it produces the following output −

+--------------+-----------------+-------------+
| soundex_Had  |   soundex_Hadi  | similarity  |
+--------------+-----------------+-------------+
|    H300      |    H300         |      4      |
+--------------+-----------------+-------------+ 

Example

Following is an example which returns a difference value of 3, having the less possible difference between the two expressions −

SELECT SOUNDEX('cool') AS soundex_cool, SOUNDEX('pool') AS soundex_pool, 
DIFFERENCE('cool', 'pool') AS similarity;

Output

On executing the above statement it produces the following output −

+---------------+-----------------+------------+
| soundex_cool  |  soundex_pool   | similarity |
+---------------+-----------------+------------+    
|    C400       |       P400      |     3      |
+---------------+-----------------+------------+

Example

Following is an example which returns a difference value of 2, having the medium possible difference between the two expressions −

SELECT SOUNDEX('Jam') AS soundex_Jam, SOUNDEX('kisan') AS soundex_kisan, 
DIFFERENCE('Jam', 'kisan') AS similarity;

Output

On executing the above statement it produces the following output −

+--------------+-----------------+-------------+
| soundex_Jam  | soundex_kisan   | similarity  |
+--------------+-----------------+-------------+
|    J500      |       k250      |      2      |
+--------------+-----------------+-------------+

Example

Following is an example which returns a difference value of 1, having the high possible difference between two expressions −

SELECT SOUNDEX('Javascript') AS soundex_Javascript, SOUNDEX('SQL') AS soundex_SQL, 
DIFFERENCE('Javascript', 'SQL') AS similarity;

Output

On executing the above statement it produces the following output −

+---------------------+-----------------+-------------+
| soundex_Javascript  | soundex_SQL     | similarity  |
+---------------------+-----------------+-------------+
|    J126             |       S400      |      1      |
+---------------------+-----------------+-------------+

Example

Following is an example which returns a difference value of 0, having the highest possible difference between two expressions −

SELECT SOUNDEX('Javascript') AS soundex_Javascript, SOUNDEX('C++') AS soundex_SQL, 
DIFFERENCE('Javascript', 'C++') AS similarity;

Output

On executing the above statement it produces the following output −

+---------------------+-----------------+-------------+
| soundex_Javascript  | soundex_C++     | similarity  |
+---------------------+-----------------+-------------+
|    J126             |       C000      |      0      |
+---------------------+-----------------+-------------+

Example

Now, lets try this function by passing the column names as arguments. First of all assume we have created a table named CUSTOMERS as shown below −

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)
);

Now let's insert records into it using the INSERT statement as follows:−

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);

Following query retrieves the difference between the first name and last name in the customers table −

Output

+----+------------+--------------+------+
| ID | FIRST_NAME | LAST_NAME    | DIFF |
+----+------------+--------------+------+
|  2 | Ramesh     | Ramesh       |    2 |
|  3 | Khilan     | Rampal            2 |
|  3 | kaushik    | Sharma       |    1 |
|  5 | Chaitali   | Ramachandran |    3 |
+----+------------+--------------+------+