0

first, here is how the mysql data looks like:

"group" column in "table_a"
---------------
user1 = 123,456,789
user2 = 789,897,12
user3 = 789
user4 = 4789,123,456
---------------

I am trying to return all users who have "789" in their group column field.

I have:

SELECT * from table_a WHERE group LIKE %789%

this query returns user1,user2,user3,user4 (user4 should not be part of the results)

and:

SELECT * from table_a WHERE group LIKE %789%

only returns user3

How can I return only user1, user2 and user3?

GMB
  • 216,147
  • 25
  • 84
  • 135
youslippin
  • 21
  • 4

1 Answers1

1

Obviously your query is missing single quotes around the right operand of like:

where grp like '%789%'

However please note that this is not a reliable to check if a value belongs to a comma-separated list, since it would partially match on any value. If your list is like '123,45,67890', then the like expression would match, while you probably don't want that.

I would recommend find_in_set():

where find_in_set('789', grp)

Finally: you should not have a data model where multiple numbers are stored in a single string columns. Instead, you should have another table, where each value is stored on a separate row. More about this can be read in this famous SO question.

Note: group is a language keyword, hence not a good choice for a column name; I renamed it to grp in the above code snippets;

GMB
  • 216,147
  • 25
  • 84
  • 135