Home > front end >  PostgreSQL: Partial match between string columns
PostgreSQL: Partial match between string columns

Time:04-28

I have the following code:

with my_table (id, col_1, col_2)
as (values 
(1, '$ (USD)', '$ (USD) million'),
(2, '$ (USD)', '$ (USD)'),
(3, 'USD', '$ (USD)'),
(4, 'EUR', '$ (USD)')
)

select *,
     case when col_1 = col_2 then 'TRUE'
     else 'FALSE'
     end as is_a_match
from my_table

the output:

id   col_1      col_2              is_a_match
1    $ (USD)    $ (USD) million    FALSE
2    $ (USD)    $ (USD)            TRUE
3    USD        $ (USD)            FALSE
4    EUR        $ (USD)            FALSE

how can I edit the query so I get id 1,2,3 as TRUE and id 4 as FALSE? I would like it to be generic, so if there are other currencies it can also look for a partial match.

CodePudding user response:

You can use strpos() to check if the string in col_1 occurs anywhere in col_2:

select *,
       strpos(col_2, col_1) > 0 as is_a_match
from my_table
  • Related