CODEWITHSUNDEEP
postgresqljoinsql-like
rshar
rshar

Reputation: 1475

How to use special characters while joining tables using ilike or like in postgres?

I have following tables in postgres:

TABLE1:

col1        col2
NCT03009058 nivolumab (opdivo)  
NCT03009059 pembrolizumab (keytruda)
NCT03009060 zumab
NCT03009061 pembrolizumab-keytruda

TABLE2:

col1    col2      
1   nivolumab
2   opdivo
2   pembrolizumab
3   keytruda
3   zumab
4   pembrolizumab
3   keytruda
5   0.4% lidocaine hydrochloride and 5% dextrose injection

I would like to join above tables on col2 but the 'like' and 'ilike' are ignoring the rows where there are special characters. I would like to use combination of special characters while joining like '|;|-|/|(|)' How can I achieve below results?

The desired output is:

col1        col2                          col3
NCT03009058 nivolumab (opdivo)        nivolumab 
NCT03009058 nivolumab (opdivo)        opdivo
NCT03009059 pembrolizumab (keytruda)  keytruda
NCT03009059 pembrolizumab (keytruda)  pembrolizumab 
NCT03009060 zumab                     zumab
NCT03009061 pembrolizumab-keytruda    keytruda
NCT03009061 pembrolizumab-keytruda    pembrolizumab

Any suggestions here would be really helpful. Thanks

Upvotes: 0

Views: 252

Answers (2)

rshar
rshar

Reputation: 1475

So I found a solution to this issue.. Instead of using to_tsquery, I used plainto_tsquery and it worked!!

Upvotes: 0

pifor
pifor

Reputation: 7892

I have something close with:

select * 
from table1 join table2
on to_tsvector(table1.col2) @@ to_tsquery(table2.col2);
    col1     |           col2           | col1 |     col2      
-------------+--------------------------+------+---------------
 NCT03009058 | nivolumab (opdivo)       |    1 | nivolumab
 NCT03009058 | nivolumab (opdivo)       |    2 | opdivo
 NCT03009059 | pembrolizumab (keytruda) |    3 | pembrolizumab
 NCT03009061 | pembrolizumab-keytruda   |    3 | pembrolizumab
 NCT03009060 | zumab                    |    3 | zumab
 NCT03009059 | pembrolizumab (keytruda) |    3 | keytruda
 NCT03009061 | pembrolizumab-keytruda   |    3 | keytruda
 NCT03009059 | pembrolizumab (keytruda) |    4 | pembrolizumab
 NCT03009061 | pembrolizumab-keytruda   |    4 | pembrolizumab
(9 rows)

Upvotes: 2

Related Questions