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

Question

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

Word

O

R

A

C

L

E

Razvan · Accepted Answer · 2014-02-16 09:42:32Z

3

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

edited Feb 16, 2014 at 9:42

answered Feb 16, 2014 at 8:41

Razvan

2,60621 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Villinda Over a year ago

I have gone through couple of other ways of hierarchical querying also... Seems these iterations can be done with CONNECT BY only... Any suggestion on whether it can be done with decode?

Razvan Over a year ago

You could do it with decode as well, but you would still have to use CONNECT BY. But you could also do it with SUBSTR (without the need of regexp). See my updated answer.

Villinda Over a year ago

Got an another way which can be used in any version of ORACLE.. SELECT substr('ORACLE', rownum, 1) FROM all_objects WHERE rownum <= (SELECT length('ORACLE') FROM dual)

Razvan Over a year ago

Yes, but this will not work if there are less than 6 objects; still, it's an alternative for older versions.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related