Matching words in string

Question

• I have a MySQL table that holds questions for users.
  • Each user has a category assigned to them.
  • Each question can target one or more user categories.
  • The question's category is in a column on the questions table, and not in a separate table.
  • For questions that target multiple user categories, each category will be separated by a character such as |

I'm trying to get all the questions from the table where a specific user's category matches that of the string that is in the category column for a question.

I've ran into a couple issues:

$string^ will not work because the string I'm trying to compare with may be something like Retail|Field Based

I can't use a WHERE category LIKE "%'.$category.'%" because some categories share words. For example: Retail and Retail Roadshow If Retail was the category, it would also match on Retail Roadshow

I'm at a bit of a loss on how to do this with the current database structure. I don't plan on changing the structure of the database, but may have to if I cannot get a working answer for how it works currently.

Answer 1

You have three choices:

1) split the wildcard matching into a per-word business:

foo LIKE '%bar baz%' -> foo LIKE '%bar%' AND foo LIKE '%baz%'

2) Use a fulltext index

foo like '%bar baz%' -> MATCH(foo AGAINST 'bar baz')

3) Or normalize your table so each keyword is its own child record:

JOIN keywords ON ...
WHERE keywords.word IN ('bar', 'baz')

Answer 2

As others already mentioned, databases work best with tables.

But for the sake of argument, let's see how we can achieve your goal without any DB refactor.

First of all you'll need to replace all your pipes with commas.

replace(column, '|', ',')

Now use find_in_set on the results:

find_in_set('Retail', replace(column, '|', ','))

MySQL indexes start with 1, so you can check if Rrtail exists in column with:

find_in_set('Retail', replace(column, '|', ',')) > 0