Skip to content

How to change my name into columns using sql

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

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