FIND_IN_SET question or..

Question

Hello. Anyone could help me with a mysql Question? I have a row with a list of items, eg: '1','2','3','4','5','6','7','8','9' .. sets of 3 elements.. from 3 to 3.. Now how could i search into this with FIND_IN_SET above '4' and return from what sets it is. In that case, to tell me that is from set 2

SELECT FIND_IN_SET('''4''', REPLACE(`some`, ' ', '')) FROM `x` WHERE `id` = 2

with that one .. it return me the position .. but now, i want to return a number of set where 4 is come from

'1','2','3' is one set, '4','5','6' is second and '7','8','9' is last

Answer 1

Assuming your sets always have length 3, you can use integer division on the result of FIND_IN_SET (but remember it is one-based so you need to adjust for that):

(x - 1) DIV 3 + 1

So your expression becomes:

(FIND_IN_SET('''4''', REPLACE(`some`, ' ', '')) - 1) DIV 3 + 1

Note that this now returns 1 instead of 0 for elements that are not found. If this is a problem then you can test for this case separately or use this expression instead:

(FIND_IN_SET('''4''', REPLACE(`some`, ' ', '')) + 2) DIV 3