Replacing numbers on a comma delimited result on MYSQL

Question

I have this query:

SELECT delMonth FROM futRoot

This gives the result:

By any chance, is there a way that I can produce result, changing the numbers there to shortened month names? Using the image, the desired result would be:

Mar,Jun,Sept,Dec
Feb,Apr,Jun,Aug,Oct,Dec 
Feb,Apr,Jun,Aug,Oct,Dec
Feb,Apr,Jun,Aug,Oct,Dec
Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

I am aware that this code can produce the shortened month:

DATE_FORMAT(STR_TO_DATE(monthNumber, '%m'), '%b')

My prob is that how can I get the monthNumber from the string, then CONCAT them in a way after converting them to shortened month.

Any help will be appreciated. Thanks!

Fix your data model so you are not storing lists of ids in a string. This is a very, very, very bad way to store lists. Hint: use junction tables. — Gordon Linoff, Jun 30 '16 at 11:10
Hello Gordon. As much as I want to, I am unable to change anything on the database and how data are saved. :( I will take note of your suggestion for reference in the future. — Caress Castañares, Jul 01 '16 at 06:41

score 2 · Accepted Answer · edited May 23 '17 at 12:16

This is a hackish solution, but it works and it's just about legible because you only have twelve possible values.

SELECT TRIM(TRAILING ',' FROM CONCAT(
    CASE WHEN FIND_IN_SET(1, delMonth) THEN 'Jan,' ELSE '' END,
    CASE WHEN FIND_IN_SET(2, delMonth) THEN 'Feb,' ELSE '' END,
    CASE WHEN FIND_IN_SET(3, delMonth) THEN 'Mar,' ELSE '' END,
    CASE WHEN FIND_IN_SET(4, delMonth) THEN 'Apr,' ELSE '' END,
    CASE WHEN FIND_IN_SET(5, delMonth) THEN 'May,' ELSE '' END,
    CASE WHEN FIND_IN_SET(6, delMonth) THEN 'Jun,' ELSE '' END,
    CASE WHEN FIND_IN_SET(7, delMonth) THEN 'Jul,' ELSE '' END,
    CASE WHEN FIND_IN_SET(8, delMonth) THEN 'Aug,' ELSE '' END,
    CASE WHEN FIND_IN_SET(9, delMonth) THEN 'Sep,' ELSE '' END,
    CASE WHEN FIND_IN_SET(10, delMonth) THEN 'Oct,' ELSE '' END,
    CASE WHEN FIND_IN_SET(11, delMonth) THEN 'Nov,' ELSE '' END,
    CASE WHEN FIND_IN_SET(12, delMonth) THEN 'Dec,' ELSE '' END
))
FROM futRoot;

The months will always appear in date order, regardless of the order they appear in the numerical string.

As has been pointed out in the comments, this (and many other problems) would be much easier to solve if you redesigned the database to remove the comma separated field. See Is storing a delimited list in a database column really that bad? for more details.

Thanks for the info :D As much as I want to redesign the database, changing it would affect many systems. I tested your code and it helped. Thanks! — Caress Castañares, Jul 01 '16 at 03:35

score 1 · Answer 2 · answered Jul 01 '16 at 12:01

You can just use a bunch of replace() statements. For instance:

select replace(replace(replace(delMonth, '12', 'Dec'), '11', 'Nov', '10', 'Oct')

You just need to extend this to all 12 months. Doing the months in descending order prevents 12 from turning into JanFeb.

Replacing numbers on a comma delimited result on MYSQL

2 Answers2