1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| WITH T AS (SELECT 'Un, Trois, Cinq, Six' || ',' AS l FROM DUAL
UNION ALL SELECT 'Un, Deux, Quatre' || ',' FROM DUAL
UNION ALL SELECT 'Trois, '|| ',' FROM DUAL
UNION ALL SELECT 'Sept, Huit, ' || ',' FROM DUAL
UNION ALL SELECT 'Un, Six' || ',' FROM DUAL
),
M AS (SELECT l,
TRIM(SUBSTR(l, 1, INSTR(l, ',', 1, 1) -1 )) AS mot1,
TRIM(SUBSTR(l, INSTR(l, ',', 1, 1) +1, INSTR(l, ',', 1, 2) - INSTR(l, ',', 1, 1) - 1)) AS mot2,
TRIM(SUBSTR(l, INSTR(l, ',', 1, 2) +1, INSTR(l, ',', 1, 3) - INSTR(l, ',', 1, 2) - 1)) AS mot3,
TRIM(SUBSTR(l, INSTR(l, ',', 1, 3) +1, INSTR(l, ',', 1, 4) - INSTR(l, ',', 1, 3) - 1)) AS mot4
FROM t)
SELECT mot1 FROM M WHERE mot1 IS NOT NULL
UNION
SELECT mot2 FROM M WHERE mot2 IS NOT NULL
UNION
SELECT mot3 FROM M WHERE mot3 IS NOT NULL
UNION
SELECT mot4 FROM M WHERE mot4 IS NOT NULL
ORDER BY 1 |
Partager