0

I'm almost new to mysql.

I wanted to write a query to search for specific keywords in a column where keywords are separated by the comma. but as I use the following code, it only returns the rows where I only have that specific keyword, not in combination with any other keywords.

In Table q16, I'm looking for a way to select rows that have my keyword in the "Area_of_concern" column, no matter if it's combined with other keywords or not:

 SELECT * 
 FROM `q16` 
 WHERE area_of_concern like '%more education is needed%'

Here's an input example:

q16_id area of concern

1   more education is needed
2   more enforcement, change in strategy
3   change in strategy
4   more education is needed, change in strategy
5   transportation issue, more enforcement, more education is needed

Where I'm looking to get the rows with the keyword "more education is needed". So I should see row 1, 4,5 in the output

ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
Abedeh Golidoux
  • 1
  • 1
  • 1
    Please provide (add to your question by using [Edit]) some sample data and the desired output. – PM 77-1 Apr 18 '18 at 17:46
  • could you please add input and expected output in your question ? – Alpesh Jikadra Apr 18 '18 at 17:46
  • Here's a sample input: q16_id area of concern 1 more education is needed 2 more enforcement, change in strategy 3 change in strategy 4 more education is needed, change in strategy 5 transportation issue, more enforcement, more education is needed – Abedeh Golidoux Apr 18 '18 at 17:50
  • Have you considered using FULLTEXT Search? – neildt Apr 18 '18 at 18:11
  • I created a table named survey: create Table survey( survey_id INT unsigned not null, keyword varchar(128) ); from q16 Join survey where survey.keyword=q16.Area_of_Concern ON survey.keyword NOT IN ('more education', 'more enforcement in place') GROUP BY q16.Area_of_Concern Then I get this error: Unrecognized keyword. (near "NOT IN" at position 125) – Abedeh Golidoux Apr 18 '18 at 18:39

1 Answers1

0

I think you should create a table where you have one column for keywords and one column for where those keywords are used: a foreign key for the q16 table in your case.

It will work much faster that way.

As for your question it is a duplicate of this one here, I believe.

How to search for rows containing a substring?

A quick try: try using double quotes instead of single ones, as in some systems, single quotes don't allow for escapes (special characters) inside them.

fullmooninu
  • 950
  • 3
  • 9
  • 26
  • Right. This is basic normalisation. Stuffing discrete data into arbitrary columns that don't enforce integrity makes for slow and error-prone databases. What if someone made a typo in that keyword in that freely editable string, for example? – underscore_d Apr 18 '18 at 17:54