Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

One column in my table stores list of values separated by comma (I know it's a poor design, but refactoring is not an option at the current moment). I seem to come up with a working solution but I'm afraid it's not very efficient :

select distinct test_id , regexp_substr(str_data,'[^,]+', 1, level)  
select 1 as test_id, '1,2,3' as str_data from dual 
union all 
select 2 as test_id, '4,5,6' as str_data from dual
regexp_substr(str_data, '[^,]+', 1, level) is not null

I don't like that I have to use distinct . Is there a better approach ?

Thank you.

share|improve this question
up vote 2 down vote accepted

Your CONNECT BY joins every row to every other (including itself) N times. This is a multi-auto-cross join. I can see how it could behave poorly performance-wise when the number of records increases.

I suggest a query like this instead:

SQL> SELECT test_id, regexp_substr(str_data, '[^,]+', 1, lvl)
  2    FROM (SELECT 1 AS test_id, '1,2,3' AS str_data
  3            FROM dual
  4          UNION ALL
  5          SELECT 2 AS test_id, '4,5,6' AS str_data FROM dual) test_data
  7      ON lvl <= length(str_data) - length(REPLACE(str_data, ',')) + 1;

------- -------------------------------------
      1                                     1
      2                                     4
      1                                     2
      2                                     5
      1                                     3
      2                                     6

Replace 1000 by the maximum number of elements (or calculate it with an inner COUNT subquery). Each row will only be queried n times (where n is the number of commas).

Also if performance is critical, use INSTR and SUBSTR instead of REGEX.

share|improve this answer
Thank you; I realized my initial attempt joined strings too many times, but didn't find out how to avoid it. – a1ex07 Apr 2 '13 at 15:04

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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