I have a table like below

|   ID     |    Numbers       |  
|   6      |  3245,78765,1234,43566  |  
|   7      |  8245,88093,8326,92365  | 

I want a query that can produce the below

|     Results         |
|   3245_sub_6       | 
|   78765_sub_6       |
|   1234_sub_6       |
|   43566_sub_6      |
|   8245_sub_7       |
|   8326_sub_7      |
|   92365_sub_7     |

I am trying to split values in the above table into something like below; like concat(splited value,'','sub','',id) something like that.

Can't seem to get how to write this query. Been beating myself up for days. Please Help

  • It is usually unwise to store multiple values in a commalist instead of in separate rows, usually involving a separate table. Heed this advice next time.
    – Rick James
    Oct 3, 2018 at 0:13

1 Answer 1

SELECT DISTINCT CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(numbers,',',num),',',-1),'_sub_',id) results
FROM `table`, (SELECT 1 num UNION ALL
               SELECT 2     UNION ALL
               SELECT 3     UNION ALL
               SELECT 4) nums

The numbers count in nums subquery is to be equal or greater than max values count in CSV field. If values count is fixed and numbers count is equal to it, you may remove DISTINCT.

If MySQL version is 8+, the numbers pseudotable can be generated in CTE.

  • Superb answer. Works like a Charm.... Interesting.. I have been at this for days. Am not sure I would have been able to write this query like forever without your help.....
    – tay121222
    Sep 4, 2018 at 11:29
  • Please don't forget to accept the answer if it worked for you.
    – Dave
    Sep 4, 2018 at 14:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.