Splitting letter in a string to separate row in MYSQL

Question

I have days(working days for the school) field in school_calendar table as given below

select days from school_calendars where id=1;

returns MTWHF as result

I want the output as followed

+---------+  
  Days
+---------+
|    M    |
|    T    |
|    W    |
|    H    |
|    F    |
+---------+

I formatted your tables. But guess what you can do it yourself too :D Just indent it with the **{ }** brackets in the code editor. Mark your whole code / tables and indent them :) — BoJack Horseman, Oct 22 '15 at 13:41
See this for a guide to a possible solution http://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows — PaulF, Oct 22 '15 at 14:13

score 0 · Answer 1 · answered Oct 22 '15 at 14:26

Doing it for a string up to 1000 characters long:-

SELECT SUBSTR(days, aNum, 1) AS aDay
FROM school_calendars 
INNER JOIN
(
    SELECT 1 + units.aCnt + tens.aCnt * 10 + hundreds.aCnt * 100 AS aNum
    FROM
    (SELECT 0 AS aCnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units.
    (SELECT 0 AS aCnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens,
    (SELECT 0 AS aCnt UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds
) sub0
ON sub0.aNum <= LENGTH(days)
WHERE id=1;

For a short varchar(7) that can be simplified to

SELECT SUBSTR(days, aNum, 1) AS aDay
FROM school_calendars 
INNER JOIN
(
    SELECT 1 AS aNum UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 
) sub0
ON sub0.aNum <= LENGTH(days)
WHERE id=1;

I get the following error : Unknown column 'sub0' in 'on clause'.while executing second query — R B H, Oct 22 '15 at 14:39
I have just tried it and it does work (I did do a minor edit just after I first posted it - you might have missed that change) — Kickstart, Oct 22 '15 at 15:21
SELECT SUBSTR(days, aNum, 1) AS aDay FROM school_calendars INNER JOIN ( SELECT 0 AS aNum UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 ) sub0 ON sub0.aNum <= LENGTH(days) WHERE id = 1; — R B H, Oct 23 '15 at 05:05

Splitting letter in a string to separate row in MYSQL

1 Answers1