Find substring in string when selecting from the MySQL database

Question

How would I take text that is a \n delimited string and output every row that contains a certain substring?

For example: DB:

product | keywords
-------------------
test    |  test\ntest1\ntest2\ntest3
test1   |  test\nblah\nblah
test2   |  tst\nblah\nblah
test3   |  testr\nblah\nblah

SELECT * FROM products WHERE

How would I write the WHERE clause to pull product test and test1?

juergen d · Accepted Answer · 2014-11-14 10:36:19Z

3

This should work

select * from your_table
where find_in_set('test', replace(keywords, '\n', ',')) > 0
or find_in_set('test1', replace(keywords, '\n', ',')) > 0

But actually you will be better off by changing the table design and don't store multiple values in a single colum.

answered Nov 14, 2014 at 10:36

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

nerdsville Over a year ago

Ah I believe I misworded it, but this looks like it would work, if it was a dynamic variable I could do something like, where find_in_set($var, replace(keywords, '\n', ',')) > 0, and use php to run the query correct?

nerdsville Over a year ago

I will keep in mind to change the db structure, I am just on a timelimit to get this task done, I am going to, however, bookmark this page because you are right, I should link the product_id to another table and use an entry for each keyword

Kickstart Over a year ago

Minor point, but if your substring can contain a comma you probably need to replace that with some kind of place holder before you replace you other delimiter with a comma.

nerdsville Over a year ago

Excellent point, luckily it can't contain a comma, the db needs some restructuring done, which I plan to do in the future, thanks everyone for your input :)

nerdsville Over a year ago

If it does need a comma, I could write some php code that replaces a keywork such as (comma) with a comma once pulled from the database, would take only slight modification to the sql query

|

Collectives™ on Stack Overflow

Find substring in string when selecting from the MySQL database

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related