Often in PostgreSQL you may want to perform a fuzzy search in which you return all rows from a table with a string that roughly matches a search string.
The easiest way to do so is by using the levenshtein() function in PostgreSQL, which calculates the Levenshtein distance between two strings.
This function returns a number that quantifies the similarity between two strings.
Strings that are exactly the same have a Levenshtein distance of 0. The greater the distance value, the greater the difference between the strings.
The following example shows how to use this function to perform a fuzzy search in PostgreSQL in practice.
Example: How to Use Fuzzy Search in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER,
team TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Celtics', 22);
INSERT INTO athletes VALUES (0002, 'Hawks', 30);
INSERT INTO athletes VALUES (0003, 'Caltics', 12);
INSERT INTO athletes VALUES (0004, 'Kings', 25);
INSERT INTO athletes VALUES (0005, 'Mavs', 31);
INSERT INTO athletes VALUES (0006, 'Celts', 16);
INSERT INTO athletes VALUES (0007, 'Hawks', 10);
INSERT INTO athletes VALUES (0008, 'Celtics', 14);
INSERT INTO athletes VALUES (0009, 'Hawks', 20);
INSERT INTO athletes VALUES (0010, 'Hawks', 26);
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+---------+--------
1 | Celtics | 22
2 | Hawks | 30
3 | Caltics | 12
4 | Kings | 25
5 | Mavs | 31
6 | Celts | 16
7 | Hawks | 10
8 | Celtics | 14
9 | Hawks | 20
10 | Hawks | 26
Suppose that we would like to perform a fuzzy search to return all rows where the string in the team column roughly matches “Celtics” as the team name.
First, we must use CREATE EXTENSION to enable the levenshtein() function in PostgreSQL:
CREATE EXTENSION fuzzystrmatch;
Next, we can use the following syntax to return all rows where the Levenshtein distance between the string in the team column and the string “Celtics” is less than or equal to 2:
SELECT *
FROM athletes
WHERE levenshtein(team, 'Celtics') <= 2;
id | team | points
----+---------+--------
1 | Celtics | 22
3 | Caltics | 12
6 | Celts | 16
8 | Celtics | 14
Notice that the resulting table contains all rows where the string in the team column roughly matches “Celtics” as the team name.
Note that you can change the 2 in the WHERE clause to a different number to allow for even more distant matching.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Remove Specific Characters from String
PostgreSQL: Find Last Occurrence of Character in String
PostgreSQL: How to Remove Substring from a String
