I have a table with below structure:
create table TBL_TEST
(
col_id NUMBER,
col_name VARCHAR2(500)
)
Some example data :
col_id | col_name
-----------------
1 | aetnap
2 | elppa
3 | ananab
What I need to do is to split characters
of column col_name
for each col_id
for example for col_id=1
we must have :
col_id | col_name
-----------------
1 | a
1 | e
1 | t
1 | n
1 | a
1 | p
this query is fine when there is only one record in the table :
SELECT col_id, REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) AS VAL
FROM tbl_test t
CONNECT BY REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) is not null
but as soon as I insert another record in the table (say col_id=2 and col_id=3) I can not have the desired result. I want to know two things:
- Why is this query works fine for one record and it does not for more ?
- what is the best way to split the characters when there is no delimiter(like , or . or *) between them?
Thanks in advance
Best Answer
1- Because with multiple rows, multiple paths exist to reach a level in the hierarchy
2- I am not saying it is the best, it is just one possible way to do it (add delimiters and use other tricks):
Here you can find multiple methods for tokenizing strings:
https://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-in-oracle/