0

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:

  1. Why is this query works fine for one record and it does not for more ?
  2. what is the best way to split the characters when there is no delimiter(like , or . or *) between them?

Thanks in advance

1

1- Because with multiple rows, multiple paths exist to reach a level in the hierarchy

select lvl, count(*) from (
SELECT col_id, REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) AS VAL, level lvl
  FROM tbl_test
CONNECT BY REGEXP_SUBSTR(col_name, '[a-z]{1}', 1, LEVEL) is not null
) group by rollup(lvl)
order by 1;

       LVL   COUNT(*)
---------- ----------
         1          3
         2          9
         3         27
         4         81
         5        243
         6        486
                  849

2- I am not saying it is the best, it is just one possible way to do it (add delimiters and use other tricks):

drop table tbl_test;
create table tbl_test
(
  col_id   number,
  col_name varchar2(500)
);

insert into tbl_test values (1, 'aetnap');
insert into tbl_test values (2, 'elppa');
insert into tbl_test values (3, 'ananab');
commit;

select 
  col_id, trim(column_value) val
from tbl_test,
  xmltable(trim(trailing ',' from regexp_replace(col_name, '(.)', '"\1",')));

COL_ID VAL
------ ---
     1 a  
     1 e  
     1 t  
     1 n  
     1 a  
     1 p  
     2 e  
     2 l  
     2 p  
     2 p  
     2 a  
     3 a  
     3 n  
     3 a  
     3 n  
     3 a  
     3 b 

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/

2
  • Could you please explain what does this part do? regexp_replace(col_name, '(.)', '"\1",')
    – Pantea
    Aug 24 '20 at 16:02
  • 1
    @Pani Converts aetnap to "a", "e", "t", "n", "a", "p",, so after the TRIM, XMLTABLE can work with it. Aug 24 '20 at 16:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.