A reader, June 03, 2008 - 4:02 pm UTC
Thanks Tom...
Good
Georg, June 03, 2008 - 5:56 pm UTC
Nice technique to remember. I read it in your answers for the third time today.
If you aren't allowed to create types you can try to use sys.odcinumberlist instead of myType.
Another option
Karthick, June 04, 2008 - 3:53 am UTC
If you want to try something without a type then here you go...
SQL> select * from test_users;
NAME COST_CENTER
------ ------------
ABC 101
XYZ 100,101,102,204,203
HJK 130,206
SQL> select name, trim(substr(cost_center, start_pos, end_pos-start_pos+1)) cost_center
2 from (select name, ','||cost_center||',' cost_center,
3 instr(','||cost_center||',',',',1,no)+1 start_pos,
4 instr(','||cost_center||',',',',1,no+1)-1 end_pos
5 from test_users,
6 (SELECT level no
7 FROM DUAL,(SELECT MAX(NVL(LENGTH(TRIM(REPLACE(TRANSLATE(COST_CENTER,'0123456789',' '),' ', '')))
,0)+1) A
8 FROM TEST_USERS)
9 CONNECT BY LEVEL <= A)
10 order by name, no)
11 where trim(substr(cost_center, start_pos, end_pos-start_pos+1)) is not null
12 /NAME COST_CENTER
------ ------------
ABC 101
HJK 130
HJK 206
XYZ 100
XYZ 101
XYZ 102
XYZ 204
XYZ 203
8 rows selected.
A reader, February 01, 2012 - 1:46 am UTC
Optimize for large tables
John, July 10, 2012 - 5:42 pm UTC
If you have a large table, you may want to prune sooner:
CREATE TABLE test_users
(
NAME VARCHAR (100) ,
cost_center VARCHAR (1000) );
Insert into TEST_USERS
(NAME, COST_CENTER)
Values
('ABC', '101');
Insert into TEST_USERS
(NAME, COST_CENTER)
Values
('XYZ', '100,101,102,204,203');
Insert into TEST_USERS
(NAME, COST_CENTER)
Values
('HJK', '130,206');
COMMIT;
SELECT name,
trim(SUBSTR(cost_center, start_pos, end_pos-start_pos+1)) cost_center
FROM
(SELECT name,
','||cost_center||',' cost_center,
instr(','||cost_center||',',',',1,no)+1 start_pos,
instr(','||cost_center||',',',',1,no+1)-1 end_pos,
no
FROM test_users,
(SELECT level no
FROM DUAL,
(SELECT max(length(trim(cost_center)) - length(replace(trim(cost_center),',','')) + 1) A
FROM TEST_USERS
)
CONNECT BY LEVEL <= A
) all_levels
where all_levels.no <= length(trim(cost_center)) - length(replace(trim(cost_center),',','')) + 1
ORDER BY name,
no
)
WHERE trim(SUBSTR(cost_center, start_pos, end_pos-start_pos+1)) IS NOT NULL;
I "borrowed" the method for finding the level from another "Ask Tom"