search exact string exist within column mysql

Question

I'm using below sql query for search values from db, but when I search 'study' it will returns the values 'caese-study', 'get-study-materials' as well. How can I use a query to search exact contains withing string column?

$names = 'study'; and the names column has values like comma separated, Ex: 'study, abc, new' so I need to search within that too

SELECT * FROM datatitle WHERE  names LIKE '%$names %' ;

SELECT * FROM datatitle WHERE  names regexp '(^|[[:space:]])$names([[:space:]]|$)';

I try with above two queries but didnt work as expect, pls advice?

stop using `LIKE` use `=`, e.g `SELECT * FROM datatitle WHERE names= '$names' ; ` — A l w a y s S u n n y, Dec 30 '18 at 15:45
@Curious_Mind names column has comma separated data so will it work for that? — , Dec 30 '18 at 15:50
@SanduniDi You should have mentioned that in the question from the start. Now I feel I have to reopen this. — Funk Forty Niner, Dec 30 '18 at 15:50
I've reopened this. Hint: You'll need to use `IN()` for this or possibly `FIND_IN_SET()` but unsure on the latter. — Funk Forty Niner, Dec 30 '18 at 15:52
Should probably normalize the table. See https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — user3783243, Dec 30 '18 at 15:57

score 2 · Answer 1 · answered Dec 30 '18 at 16:00

You should not be storing comma-separated values in a column. You should be using a junction/association table. You should fix the data model, if you can.

However, sometimes we cannot control other people's really bad decisions. MySQL has find_in_set():

SELECT dt.*
FROM datatitle dt
WHERE find_in_set(?, names) > 0;

Note that I have replaced the constant $names with a parameter. You should learn to use parameters to pass values into queries.

search exact string exist within column mysql

1 Answers1