CODEWITHSUNDEEP
sqloracle-database
Dev Ngron
Dev Ngron

Reputation: 135

Splitting a comma separated string in Oracle not working

I would like to retrieve a set of values from a comma separated string into an IN clause in a query. Does some one knows why the following code is not working properly;

WITH xtable AS (
         SELECT 1 ID, '116,117,169,170,173,175,9015,44008,44367,44446,45081,45083,46779,47161,47222' AGT FROM DUAL
         UNION ALL
         SELECT 2 ID, '456,789' AGT FROM DUAL
         UNION ALL
         SELECT 3 ID, '116,117,169,170,173,175,9015,44008,44367,44446,45081,45083,46779,47161' AGT FROM DUAL
     )      
  select regexp_substr(x.AGT,'[^,]+', 1, level)
  from xtable x
  where x.ID = 3
  connect by regexp_substr(x.AGT, '[^,]+', 1, level) is not null;

In this scenario the result should be 

    AGT
1   116
2   117
3   169
4   170
5   173
6   175
7   9015
8   44008
9   44367
10  44446
11  45081
12  45083
13  46779
14  47161

Instead I get an almost infinite loop of the same values

Upvotes: 0

Views: 564

Answers (3)

MT0
MT0

Reputation: 167981

There are multiple solutions for this in the Splitting Delimited Strings - SO Oracle Documentation page.

A solution using a correlated hierarchical query is:

WITH xtable (id, agt ) AS (
  SELECT 1, '116,117,169,170,173,175,9015,44008,44367,44446,45081,45083,46779,47161,47222' FROM DUAL
UNION ALL
  SELECT 2, '456,789' FROM DUAL
UNION ALL
  SELECT 3, '116,117,169,170,173,175,9015,44008,44367,44446,45081,45083,46779,47161' FROM DUAL
)
SELECT id,
       REGEXP_SUBSTR( x.agt, '[^,]+', 1, t.COLUMN_VALUE ) AS item
FROM   xtable x
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT LEVEL
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( x.agt, '[^,]+' )
           )
         ) AS SYS.ODCINUMBERLIST
       ) t
WHERE  x.id = 3;

Output:

ID  ITEM
-- -----
 3   116
 3   117
 3   169
 3   170
 3   173
 3   175
 3  9015
 3 44008
 3 44367
 3 44446
 3 45081
 3 45083
 3 46779
 3 47161

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

Issue in your query is that the where clause will be applied to only level 1 not any further.

Try this using nested table:

WITH xtable AS (
         SELECT 1 ID, '116,117,169,170,173,175,9015,44008,44367,44446,45081,45083,46779,47161,47222' AGT FROM DUAL
         UNION ALL
         SELECT 2 ID, '456,789' AGT FROM DUAL
         UNION ALL
         SELECT 3 ID, '116,117,169,170,173,175,9015,44008,44367,44446,45081,45083,46779,47161' AGT FROM DUAL
     )      
  select regexp_substr(x.AGT,'[^,]+', 1, t.column_value) agt
  from xtable x cross join table(
    cast(
      multiset(
        select level
        from dual
        connect by level <= regexp_count(x.AGT,',') + 1
      )as sys.odcinumberlist
    )
  ) t
  where x.id = 3;

It's a general purpose query which you can use even without where clause if you wanted to convert all of the them at once.

In Oracle 12c+, you can use OUTER APPLY to achieve the same effect and simpler syntax:

WITH xtable AS (
         SELECT 1 ID, '116,117,169,170,173,175,9015,44008,44367,44446,45081,45083,46779,47161,47222' AGT FROM DUAL
         UNION ALL
         SELECT 2 ID, '456,789' AGT FROM DUAL
         UNION ALL
         SELECT 3 ID, '116,117,169,170,173,175,9015,44008,44367,44446,45081,45083,46779,47161' AGT FROM DUAL
     )      
  select regexp_substr(x.AGT,'[^,]+', 1, t.n) agt
  from xtable x
  outer apply (
        select level n
        from dual
        connect by level <= regexp_count(x.AGT,',') + 1
  ) t
  where x.id = 3;

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191275

You have the same values in multiple rows, so your connect-by is bouncing between them. Hierarchical queries are a little tricky with multiple rows involved. If you really only want the values for a single ID you can filter in a subquery:

WITH xtable AS (
         SELECT 1 ID, '116,117,169,170,173,175,9015,44008,44367,44446,45081,45083,46779,47161,47222' AGT FROM DUAL
         UNION ALL
         SELECT 2 ID, '456,789' AGT FROM DUAL
         UNION ALL
         SELECT 3 ID, '116,117,169,170,173,175,9015,44008,44367,44446,45081,45083,46779,47161' AGT FROM DUAL
     )      
  select regexp_substr(x.AGT,'[^,]+', 1, level)
  from (select AGT from xtable where ID = 3) x
  connect by regexp_substr(x.AGT, '[^,]+', 1, level) is not null;

REGEXP_SUBSTR(X.AGT,'[^,]+',1,LEVEL)                                        
----------------------------------------------------------------------------
116
117
169
170
173
175
9015
44008
44367
44446
45081
45083
46779
47161

14 rows selected.

Or you can include id = prior id, but then also need to use a non-deterministic function call to prevent cycling:

WITH ... (...)
  select regexp_substr(x.AGT,'[^,]+', 1, level)
  from xtable x
  where x.ID = 3
  connect by id = prior id and prior dbms_random.value is not null
  and regexp_substr(x.AGT, '[^,]+', 1, level) is not null;

which gets the same 14 rows back, and would work if you included multiple IDs.

Upvotes: 1

Related Questions