Mysql contain string to get particular string in column

Question

Please suggest how to get particular string from column without using like query. example.

• Table name: notification
• Column 1: notificationid
• Column 2: notification_to_id

Sample data:

notificationid       notification_to_id
---------------------------------------
  1                        25,23   
  2                        15,16,25

How to get only 25 string alone from notification_to_id column?

Answer 1

try this one

SELECT notificationid,
       SUBSTRING_INDEX(SUBSTRING_INDEX(CONCAT(notification_to_id,','),',',value.v),',',-1) as to_id
FROM notification,(SELECT 1 as v UNION
                   SELECT 2 UNION
                   SELECT 3 UNION
                   SELECT 4 UNION
                   SELECT 5)value
HAVING to_id = '25'

sqlFiddle

So the query selects each element in the csv field by using SUBSTRING_INDEX with a value.v which will be anywhere from 1 to maximum 5 in this case ( given by the UNION of SELECT 1 ... SELECT 5 ). If it only has 3 elements in the csv field, when value.v is 4 or 5 it'll return empty string ''. And the having is to return rows that have 25 as element.

If notification_to_id contains more than 5 elements, just add more UNION like SELECT 6 and so on up to the maximum number of elements in that column.

another method is to use the REGEX like below (but you have many occurences of 25 to replace

SELECT notificationid,
       '25' as value
FROM notification
WHERE notification_to_id REGEXP '(^25$)|(^25,)|(,25$)|(,25,)'

sqlfiddle

the above query will match if 25 is the only element (^25$) ^ marks start of string $ marks end of string. OR if 25 is the first item (^25,) OR if 25 is the last item (,25$) OR if 25 is somewhere in the middle (,25,)

Or if you want to use LIKE you use the below

SELECT notificationid,
       '25' as value
FROM notification
WHERE notification_to_id LIKE '25'
   OR notification_to_id LIKE '25,%'
   OR notification_to_id LIKE '%,25'
   OR notification_to_id LIKE '%,25,%'

sqlFiddle