Skip to main content

difference

1. DIFFERENCE Function​

The DIFFERENCE function in H2 database is used to calculate the difference between two strings.

2. Syntax​

The syntax for the DIFFERENCE function in H2 database is as follows:

DIFFERENCE(str1, str2)

Arguments​

  • str1: The first string for comparison.
  • str2: The second string for comparison.

Return​

  • The DIFFERENCE function returns an integer value representing the difference between the two strings.

3. Notes​

  • The DIFFERENCE function in H2 database calculates the difference between two strings based on the Soundex algorithm.
  • The function returns a value between 0 and 4, where 0 indicates no similarity and 4 indicates a high similarity.
  • The difference value is based on the number of matching Soundex codes in the strings.
  • The function ignores case while calculating the difference.
  • The function considers only the first 255 characters of each string for comparison.
  • If either of the input strings is NULL, the function returns NULL.
  • If the input strings are empty, the function returns 4, indicating a high similarity.

4. Examples​

Here are a few examples demonstrating the usage of the DIFFERENCE function in H2 database:

Example 1 - Calculating the difference between two strings:​

SELECT DIFFERENCE('hello', 'hola') AS difference;

Output:

difference
----------
2

Example 2 - Calculating the difference between strings stored in columns:​

CREATE TABLE names (
  id INT PRIMARY KEY,
  name1 VARCHAR(255),
  name2 VARCHAR(255)
);

INSERT INTO names VALUES (1, 'John', 'Joan'), (2, 'Michael', 'Michelle'), (3, 'David', 'Diana');

SELECT id, name1, name2, DIFFERENCE(name1, name2) AS difference
FROM names;

Output:

id |  name1  |   name2  | difference
---+---------+----------+------------
1  |  John   |   Joan   |     4
2  | Michael | Michelle |     3
3  |  David  |  Diana   |     2

There are no related functions specific to the DIFFERENCE function in H2 database.