Partial and exact match of strings in postgres

Question

I have following table in postgres 10.

col1        col2    col3                                            col4        
NCT00000102 Drug    nifedipine                                      nifed   
NCT00000102 Drug    nifedipine sulphate                             nifedipine  
NCT00000103 Drug    phospho nifedipine                              ni
NCT00000103 Drug    phospho nifedipine sulphate                     phospho nifedipine      
NCT00000105 Drug    fluticasone furoate (veramyst®) nasal spray     veramyst
NCT00000105 Drug    fluticasone furoate (veramyst®) nasal spray     vera
NCT00000106 Drug    veramyst                                        veramyst

I am looking for a way to filter above table so that either only exact match or partial match but with the complete word/s included.

I am expecting below output.

col1        col2    col3                                            col4        
NCT00000102 Drug    nifedipine sulphate                             nifedipine  
NCT00000103 Drug    phospho nifedipine sulphate                     phospho nifedipine      
NCT00000105 Drug    fluticasone furoate (veramyst®) nasal spray     veramyst
NCT00000106 Drug    veramyst                                        veramyst

I tried using below query but it is not giving me the partial matches properly.

where col3 '%' || col4 || '%'

Any suggestion here will be really useful.

Did you use LIKE?

Wiktor Stribiżew
– Wiktor Stribiżew

2020-05-25 08:43:43 +00:00
Commented May 25, 2020 at 8:43 — Wiktor Stribiżew
– Wiktor Stribiżew, Commented May 25, 2020 at 8:43

Tim Biegeleisen · Accepted Answer · 2020-05-25 08:57:29Z

1

Try using regex matching:

SELECT col1, col2, col3, col4
FROM yourTable
WHERE col3 ~* CONCAT('\y', col4, '\y');

This will return every record where the word or words in col4 appear as proper word(s) in col3. If you wanted to use LIKE, you could use:

WHERE col3 LIKE '%' || col4 || '%'

but keep in mind this might return false positives for substring matches.

edited May 25, 2020 at 8:57

answered May 25, 2020 at 8:46

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rshar Over a year ago

WHERE col3 ~* '.*\y' || col4 || '\y.*'; gives below error: ERROR: argument of WHERE must be type boolean, not type text

Wiktor Stribiżew Over a year ago

.* in .*\y will make it match slower. Remove .* from the regex as ~* allows partial matches.

rshar Over a year ago

Does this where statement take only boolean. I am still getting this error.

Tim Biegeleisen Over a year ago

@rshar Use CONCAT instead, or else you might need to do some casting.

Collectives™ on Stack Overflow

Partial and exact match of strings in postgres

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related