CODEWITHSUNDEEP
sqloracle-databaseoracle11gsqlplus
John
John

Reputation: 1327

Convert each character in a string to a row

I have a column which contains a String with numbers like '12345' (no separator or blanks) and I want to split this column to rows for each character:

From:

 COLUMN
 ------------------
 12345

To:

 COLUMN
 ------------------
 1
 2
 3
 4
 5

This Should work in a single select so that I can use it like this:

... AND WHERE SOMECOLUMN NOT LIKE (... THE SELECT ...)

Upvotes: 1

Views: 1937

Answers (1)

Rob van Laarhoven
Rob van Laarhoven

Reputation: 8905

with temp as (select '12456' as str from dual)
select substr(str,level,1)
from temp
connect by level <= length(str);

Result:

1
2
4
5
6

Upvotes: 3

Related Questions