0

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.

4
  • Does this answer your question? Fuzzy Matching with Postgresql 9.3
    – mustaccio
    May 5, 2022 at 20:36
  • @mustaccio I'd like to avoid using external extensions.
    – willsbit
    May 5, 2022 at 20:55
  • In that case you'll end up writing an "internal" extension of your own, in one of the many possible forms. Suit yourself, I guess.
    – mustaccio
    May 5, 2022 at 22:09
  • Metaphone results that share two characters do not necessarily belong to words that are similar in any respect. May 6, 2022 at 6:36

1 Answer 1

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.