What is the best query to split name into columns?
ie. if my name is VELOCITY in one column.
How can i split it as
V E L O C I T Y
Advertisement
Answer
If you just have a single row then:
SELECT SUBSTR('VELOCITY', LEVEL, 1) AS ch FROM DUAL CONNECT BY LEVEL <= LENGTH('VELOCITY');
Which outputs:
CH V E L O C I T Y
If you have multiple input rows then, from Oracle 12, you can use a LATERAL
join:
SELECT c.ch FROM table_name t INNER JOIN LATERAL ( SELECT SUBSTR(t.value, LEVEL, 1) AS ch FROM DUAL CONNECT BY LEVEL <= LENGTH(t.value) ) c ON (t.value IS NOT NULL);
db<>fiddle here