CODEWITHSUNDEEP
postgresqlsearch
user3871
user3871

Reputation: 12708

search column A for a string match in column B

Using PostgreSQL, how can you search column A for a string match in column B?

Intermediate table (subquery) output:

string_a_id | string_a | string_b_id | string_b
-----------------------------------------------
1            'hello world'  11         null
2            'hello world'  13         null
3            'ipsum lorem'  21         'hello world'

Querying intermediate table above for match. Expected output:

string_a_id | string_a | string_b_id | string_b
-----------------------------------------------
1            'hello world'  21         'hello world'
2            'hello world'  21         'hello world'

I'm using

select * 
from (
    // subquery
) as subquery_results
where (trim(subquery_results.string_a) ilike trim(subquery_results.string_b))

but this returns 0 results.

Upvotes: 1

Views: 72

Answers (1)

klin
klin

Reputation: 121524

As you want to compare each string_a with any string_b you should use self-join:

with dataset(string_a_id, string_a, string_b_id, string_b) as (
values
    (1, 'hello world', 11, null),
    (2, 'hello world', 13, null),
    (3, 'ipsum lorem', 21, 'hello world')
)

select q1.string_a_id, q1.string_a, q2.string_b_id, q2.string_b
from dataset q1
join dataset q2 on trim(q1.string_a) ilike trim(q2.string_b)

 string_a_id |  string_a   | string_b_id |  string_b   
-------------+-------------+-------------+-------------
           1 | hello world |          21 | hello world
           2 | hello world |          21 | hello world
(2 rows)

Replace values in the initial query with your actual query.

Upvotes: 2

Related Questions