-1

I am trying to do a query for my mysql db. I have a column that has a comma separated value. I need to do a WHERE statement search with a value to see if it returns results.

lets say my column has a value of: = "value1, value2, value3"

"SELECT * FROM table WHERE column_name IN('value1')"

Problem is there is no results.

I don't want to do a "like" query as even a value22 would come up?

So how do I properly do a query of a comma separated column by searching with one value?

Thank you.

Dharman
  • 30,962
  • 25
  • 85
  • 135
user17283963
  • 1
  • 1
  • You should not store values in the table like you did. That mean bad architecture, that makes issues like you expecting in the feature. Normalizing DB first thing what you have to do to escape them. – Serghei Leonenco Oct 30 '21 at 01:31

1 Answers1

0

LIKE (or ILIKE if you need case-insensitive) can do the job

SELECT *
FROM table
WHERE
  (column_name LIKE '%value_1,%' OR column_name LIKE '%value_1') AND
  (column_name LIKE '%value_2,%' OR column_name LIKE '%value_2') AND
  (column_name LIKE '%value_3%,' OR column_name LIKE '%value_3')
;
Cássio
  • 649
  • 1
  • 7
  • 17
  • Thank you. I was worried that LIKE would not always be exact. Like it will query if is still has additional text at the end of the value? Like lets say value is Test...It will still query if the value is Tester ? – user17283963 Oct 30 '21 at 00:27
  • This doesn't work, As I need an exact match. the % wild is not exact. – user17283963 Oct 30 '21 at 00:39
  • Actually the best solution would be to use `REGEXP` in replacement to `LIKE`. There are a lot of edge cases to watch over: lists of no element, one, and multiple elements; keys matching first element in the list, in the middle, and in the end; repeated keys (if they happen). Ultimately split the `REGEXP` into three clauses to simplify individual key matching. – Cássio Oct 30 '21 at 00:59
  • On top of that, this column is poorly design, usually you want to have a higher normal form so as to query them properly. – Cássio Oct 30 '21 at 01:00