CODEWITHSUNDEEP
mysqlsqlstringselect
Zerium
Zerium

Reputation: 17333

How to select rows with a certain number in one of its columns?

In one of the columns of my table (say it's called foo), it stores a set of 10 numbers, e.g:

1, 5, 8, 3, 4, 6, 9, 7, 12, 15

I wish to select all rows of my table which have the number 6 in the foo column.

Well, you might say, this sounds simple enough, just do a string search on the column. But the problem is, then you would also select rows with 16 in them, as 16 contains a 6.

I also thought about searching for , 6, instead, but I realized if 6 was on the very end or front of the string, it won't match.

How can I overcome this problem?

Upvotes: 3

Views: 105

Answers (2)

John Woo
John Woo

Reputation: 263693

use MySQL built-in function called FIND_IN_SET.

SELECT *
FROM   tablename
WHERE  FIND_IN_SET('6', REPLACE(foo,' ','')) > 0

From Docs:

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. A string list is a string composed of substrings separated by “,” characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function does not work properly if the first argument contains a comma (“,”) character.

Upvotes: 6

lc.
lc.

Reputation: 116458

You can append a ', ' to the beginning and end of foo and search:

SELECT *
FROM bar
WHERE ', ' + foo + ', ' LIKE '%, 6, %'

Upvotes: 3

Related Questions