The task I faced with today was to convert a string of numbers to rows, to be specific, to a table in order to join it with other tables.
To do this, you don't need any stored procedures or advanced SQL techniques. All we're going to use is:
- Regular expressions
- Connect by clause, which does magic in many cases
So, the input string is a set of numbers, delimited with colons, e.g. '11:2:13:4:9'. For this example I will use the incorrect format to show the solution works correctly in this case as well '11:2::13:4:asd:9:'
The magic query is.....
select level, regexp_substr(str, '[0-9]+', 1, level) substr
from
(
select '11:2::13:4:asd:9:' str
from dual
)
connect by level <= regexp_count(str, '[0-9]+')
;
The result:
LEVEL SUBSTR
1 11
2 2
3 13
4 4
5 9
The regexp_substr function in the example returns the occurrence based on the level, and searches each time starting from the first character.
As you can see, only numbers were picked up. This is a result of the pattern used in regexp functions. Playing with it you can adjust the result according your needs.
Now some boring links to the documentation (in my case 11.2), which can be quite handy sometimes:
regexp_substr
regexp_count
Hierarchical Queries
To do this, you don't need any stored procedures or advanced SQL techniques. All we're going to use is:
- Regular expressions
- Connect by clause, which does magic in many cases
So, the input string is a set of numbers, delimited with colons, e.g. '11:2:13:4:9'. For this example I will use the incorrect format to show the solution works correctly in this case as well '11:2::13:4:asd:9:'
The magic query is.....
select level, regexp_substr(str, '[0-9]+', 1, level) substr
from
(
select '11:2::13:4:asd:9:' str
from dual
)
connect by level <= regexp_count(str, '[0-9]+')
;
The result:
LEVEL SUBSTR
1 11
2 2
3 13
4 4
5 9
The regexp_substr function in the example returns the occurrence based on the level, and searches each time starting from the first character.
As you can see, only numbers were picked up. This is a result of the pattern used in regexp functions. Playing with it you can adjust the result according your needs.
Now some boring links to the documentation (in my case 11.2), which can be quite handy sometimes:
regexp_substr
regexp_count
Hierarchical Queries
