Oracle REGEXP_SUBSTR returning NULL as value

Question

I am trying to split data from a column into rows but I am facing this issue here. When i run this query it splits the data fine but it is also returning NULL as an extra row too.

Here is the value I am trying to split ,162662163,90133140,163268955,169223426,169222899,

WITH CTE AS(
SELECT 

RTRIM(LTRIM(PG2.MULTILIST11, ','), ',') ACCESS_BY_GROUPS

FROM AGILE.ITEM I 
INNER JOIN AGILE.PAGE_TWO PG2 ON PG2.ID = I.ID
WHERE             

 ITEM_NUMBER IN --('313-000074', 
('313-000090')

)
SELECT DISTINCT

REGEXP_SUBSTR(ACCESS_BY_GROUPS, '[^,]+', 1, column_value) ACCESS_BY_GROUPS

FROM CTE 

CROSS JOIN TABLE(CAST(MULTISET(SELECT LEVEL FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT(CTE.ACCESS_BY_GROUPS, ',') + 10

) AS sys.odcinumberlist))

I don't want to get that null value. I cannot apply that check out of the query because it will affect other column values too so I want it to be handled somewhere in the function. I hope someone can help.

Littlefoot · Accepted Answer · 2020-03-20 11:08:16Z

2

It is because of leading and trailing commas.

One option is to begin from position 2 (see line #3) and limit number of values (line #5)

SQL> with test (col) as
  2    (select ',162662163,90133140,163268955,169223426,169222899,' from dual)
  3  select regexp_substr(col, '[^,]+', 2, level) res
  4  from test
  5  connect by level <= regexp_count(col, ',') - 1
  6  /

RES
-------------------------------------------------
162662163
90133140
163268955
169223426
169222899

SQL>

Another is to remove leading and trailing comma first, and then split the rest into rows:

SQL> with test (col) as
  2    (select ',162662163,90133140,163268955,169223426,169222899,' from dual),
  3  temp as
  4    -- remove leading and trailing comma first
  5    (select ltrim(rtrim(col, ','), ',') col
  6     from test
  7    )
  8  select regexp_substr(col, '[^,]+', 1, level) res
  9  from temp
 10  connect by level <= regexp_count(col, ',') + 1;

RES
------------------------------------------------
162662163
90133140
163268955
169223426
169222899

SQL>

[EDIT - for more than a single row]

If there are more rows involved, code has to be changed. Note that there must be some kind of a unique identifier for every row (ID in my example).

SQL> with test (id, col) as
  2    (select 1, ',162662163,90133140,163268955,169223426,169222899,' from dual union all
  3     select 2, ',1452761,1452762,' from dual
  4    )
  5  select id,
  6         regexp_substr(col, '[^,]+', 2, column_value) res
  7  from test cross join table(cast(multiset(select level from dual
  8                                           connect by level <= regexp_count(col, ',') - 1
  9                                          ) as sys.odcinumberlist))
 10  order by id, column_value
 11  /

        ID RES
---------- -------------------------------------------------
         1 162662163
         1 90133140
         1 163268955
         1 169223426
         1 169222899
         2 1452761
         2 1452762

7 rows selected.

SQL>

edited Mar 20, 2020 at 11:08

answered Mar 20, 2020 at 10:38

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Muhammad Asim Over a year ago

This is not working when there is another column having less than this column values.

Muhammad Asim Over a year ago

CONNECT BY LEVEL <= REGEXP_COUNT(NVL(ENABLED_ORG, CTE.ACCESS_BY_GROUPS), ',') + 1

Littlefoot Over a year ago

Well, you didn't say that initially. There was just a single comma-separated string you mentioned. Anyway, I added some more code into the answer. Have a look, please.

Alex Poole Over a year ago

The ltrim(rtrim(col, ','), ',') could be done with a single trim() call: select trim(both ',' from col) col from test - no functional difference but maybe slightly clearer (I'm sure opinions will differ on that though!)

Littlefoot Over a year ago

You'll then have difficulties if you'll have to join those values with some other tables. But - try it. Anyway - now you see how to split column into rows, the rest is up to you.

|

Collectives™ on Stack Overflow

Oracle REGEXP_SUBSTR returning NULL as value

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related