Caress Castañares Caress Castañares - 5 months ago 14
SQL Question

Replacing numbers on a comma delimited result on MYSQL

I have this query:

SELECT delMonth FROM futRoot


This gives the result:

enter image description here

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!

Answer

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.