CODEWITHSUNDEEP
sqlstringpostgresqlwhere-clause
Jeremy
Jeremy

Reputation: 113

Select where any word in string of column A matches any word in string of column B

I've been searching the website for some guidance, but I haven't been able to find something that fits. I was wondering if there is a way to select rows where "any word in a string in column A matches any word in a string in column B"? I am using postgresql.

ID | Name              | Name_of_kin 
1  | Peter Reeves      | John Reeves
2  | Emily Sanchez     | Roger James
3  | Rico van Der      | Moses van Fel
4  | Monic             | Monica
5  | Michaelangelo     | Michael

In this scenario, I want to select rows where any word in Name_of_kin matches any word in Name. The outcome I am hoping to achieve is:

ID | Name              | Name_of_kin 
1  | Peter Reeves      | John Reeves
3  | Rico van Der      | Moses van Fel

A big thank you in advance!

Upvotes: 2

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this with regular expressions:

select t.*
from mytable t
where t.name ~ ('(\W|^)' || replace(t.name_of_kin, ' ', '(\W|$)|(\W|^)') || '(\W|$)');

Or:

select t.*
from mytable t
where ' ' || t.name || ' ' ~ (' ' || replace(t.name_of_kin, ' ', ' | ') || ' ')

Or:

select t.*
from mytable t
where t.name ~ replace(t.name_of_kin, ' ', '|') and
      t.name_of_kin ~ replace(t.name, ' ', '|');

Here is a db<>fiddle.

Upvotes: 0

GMB
GMB

Reputation: 222412

You could turn each string to an aray of words and check if they overlap:

select t.*
from mytable t
where string_to_array(t.name, ' ') && string_to_array(t.name_of_kin, ' ')

Demo on DB Fiddle:

id | name         | name_of_kin  
-: | :----------- | :------------
 1 | Peter Reeves | John Reeves  
 3 | Rico van Der | Moses van Fel

In Snowflake, you would write this as:

select t.*
from mytable t
where array_overlap(split(t.name, ' '), split(t.name_of_kin, ' '))

Upvotes: 3

Related Questions