Select all rows in table A that contains keyword in table B - SQL Redshift

userDU3j 0
Viewed 41

On a Redshift database, I have a SQL table A with hundred of thousands of rows. This table has a field "name" containing strings. Something like:

|           Name         |
| ---------------------- |
| Thor                   |
| Spiderman              |
| The Amazing Spiderman  |
| Superman               |
| Hulk                   |
| Jonas The Fish         |
| Fish Billy             |

I also have a table B containing thousands of rows with a field name "Keywords" and containing... keywords. Something like:

|        Keyword      |
| --------------------|
| Amazing Spiderman   |
| Fish                |

I want to select all rows from table A that contains a keyword within table B. So my result should be something like:

|           Name         |
| ---------------------- |
| The Amazing Spiderman  |
| Jonas The Fish         |
| Fish Billy             |

What would be the correct way to make it happen please?

Thank you

amazon-redshift sql
1 Answers

You can concatenate the wildcard character with table_B.keyword

The concatenation operator and the wildcard character may vary depending on the database

MySQL, MariaDB

SELECT Table_A.name 
FROM Table_A, Table_B
WHERE Table_A.name LIKE CONCAT('%', CONCAT(Table_B.keyword,'%'))

SQL Server

SELECT Table_A.name 
FROM Table_A, Table_B
WHERE Table_A.name LIKE '%' + Table_B.keyword + '%'

Oracle DB

SELECT Table_A.name 
FROM Table_A, Table_B
WHERE Table_A.name LIKE '%' || Table_B.keyword || '%'
iggict0
Related