Search code examples
postgresqljoinsql-like

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


Solution

  • 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)