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 '18 at 0:13
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.

| improve this answer | |
  • 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..... – user9581732 Sep 4 '18 at 11:29
  • Please don't forget to accept the answer if it worked for you. – Dave Sep 4 '18 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.