CODEWITHSUNDEEP
oracle10g
Villinda
Villinda

Reputation: 1

SQL script for splitting a string into rows containing one character each?

How can a word like 'ORACLE' be displayed as rows like this, using only one SQL statement?

Word

O

R

A

C

L

E

Upvotes: 0

Views: 72

Answers (1)

Razvan
Razvan

Reputation: 2596

You could use the CONNECT BY condition:

SELECT SUBSTR('ORACLE', level, 1)
FROM dual
CONNECT BY level <= LENGTH('ORACLE');

Edit 1: As per Alex Poole's suggestion, I replaced regexp_substr('ORACLE', '.', 1, level) IS NOT NULL with level <= LENGTH('ORACLE');

Edit 2: I replaced regexp_substr with substr

DEMO

Upvotes: 3

Related Questions