Asad Mahmood Asad Mahmood - 20 days ago 5
SQL Question

How to find a specific word within a phrase?

I wish to know how to find a specific word within a phrase. I am trying to find the word "Pizza" within a set of keywords, however there is no keyword that only has "Pizza". There are keywords such as "Pizza Delivery" and "Pizza Delivery Boy", however they won't show up! How can I do this?

Desired output:

MOVIE KEYWORD
----------------------------------- ----------------------------------
Drive Angry Waitress
Taken France
Saving Private Ryan France
30 Minutes or Less Pizza Delivery
30 Minutes or Less Pizza Delivery Boy


My script:

SELECT MovieTitle AS "MOVIE", KEYWORDDESC AS "KEYWORD"
FROM TBLMOVIE
JOIN TBLKEYWORDDETAIL ON TBLMOVIE.MOVIEID = TBLKEYWORDDETAIL.MOVIEID
JOIN TBLKEYWORD ON TBLKEYWORDDETAIL.KEYWORDID = TBLKEYWORD.KEYWORDID
WHERE TBLKEYWORD.KEYWORDDESC IN ('France', 'Waitress', 'Pizza');


My output:

MOVIE KEYWORD
----------------------------------- ----------------------------------
Drive Angry Waitress
Taken France
Saving Private Ryan France

Answer

One method uses LIKE:

WHERE TBLKEYWORD.KEYWORDDESC LIKE '%France%' OR
      TBLKEYWORD.KEYWORDDESC LIKE '%Waitress%' OR
      TBLKEYWORD.KEYWORDDESC LIKE '%Pizza%'

Another method uses REGEXP_LIKE():

WHERE REGEXP_LIKE(TBLKEYWORD.KEYWORDDESC, 'France|Waitress|Pizza')

If you use REGEXP_LIKE() you should spend a little bit of time learning about regular expressions and how to use them.