The question:
I have some data like this:
metaphone | lag |
---|---|
FLKSW | [null] |
PPS | FLKSW |
PPS | PPS |
PSP | PPS |
And I want to compare the string values in both columns on the folowing condition: they’re similar (assign some value, like 1) if they share at least 2 chars. Otherwise, they’re not similar.
So in the example, PPS and PSP would be similar.
How can this substring comparison be achieved?
I know one approach would be to extract substrings and manually compare them, but it feels hacky and I don’t know the maximum number of chars that can occur.
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
they’re similar … if they share at least 2 chars.
Unfortunately, there is no built-in “intersect” operator or function for strings or arrays. You can roll your own function to count overlapping characters:
CREATE FUNCTION f_count_overlapping_char(text, text)
RETURNS int
LANGUAGE sql PARALLEL SAFE IMMUTABLE STRICT AS
$func$
SELECT count(*)::int
FROM (
SELECT unnest(string_to_array($1, NULL))
INTERSECT ALL
SELECT unnest(string_to_array($2, NULL))
) sub;
$func$;
INTERSECT ALL
includes duplicate matching characters. To fold duplicates, use just INTERSECT
instead.
Then your query can be:
SELECT *, f_count_overlapping_char(t1.metaphone, t2.metaphone) AS overlap
FROM tbl t1
JOIN tbl t2 ON t1.id < t2.id
AND f_count_overlapping_char(t1.metaphone, t2.metaphone) >= 2;
db<>fiddle here
But it’s expensive and does not scale well with more rows in the table – O(N²). Depending on your actual objective there are various superior alternatives – like trigram similarity provided by the additional module pg_trgm. See:
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0