So say I have two tables, each with one column (for simplicity):

Google Search Queries	Pop Culture Categories (Name, Event, etc)
Betty White Death	BTS
Grown Ups 2 ending	Miley Cyrus

My goal is to join both tables to match the Google Search Query, with the Pop Culture Category. For example:

Google Search Queries	Pop Culture Categories
Betty White Death	Betty White
Grown Ups 2 ending	Grown Ups 2

I attempted to do this by this Join Condition:

SELECT (columns)
FROM Google Search Queries
JOIN Pop Culture Categories ON Query LIKE CONCAT('%', Pop Culture, '%')

The problem is, it pulls in categories within the word of a query, such as BET (like the awards) or OWN (Oprah Winfrey Network). See below:

Google Search Queries	Pop Culture Categories
Betty White Death	Betty White
Betty White Death	BET
Grown Ups 2 ending	Grown Ups 2
Grown Ups 2 ending	OWN

Is there a way I could simply join the query to the full category, without having it pull out categories that are within a word of the search query?

CodePudding user response：

If you are sure that the category name is always a subset of the Google Search Queries and the GSQ never starts with somehting different that is not the category, then you can just get rid of the initial % in the LIKE.

CodePudding user response：

You'll need (at a minimum) an ON condition that uses a regular expression. To do this right, you'd probably want to pre-process the search terms to extract relevant terms, correct common misspellings, etc. To simply match and avoid matching partial words, you can do this (the one at the bottom is what it needs - the others show why it's necessary to do it that way):

select REGEXP_INSTR( 'Betty White' , 'BET'  , 1 , 1, 0 , 'im') > 0; -- Returns TRUE. Matches the substring - similar to ilike