CODEWITHSUNDEEP
sqloracle-databaseregexp-substr
Lee Murray
Lee Murray

Reputation: 489

regexp_substr to bring back data before a foward slash

I have the following pattern of characters in a dataset. I need to manipulate the data & cross refer it to another table. I'm trying to write a regexp_substr to bring back data before a foward slash starting from the left. for example:-

abc/ab/123/zzz

so I need to get the following results back to then compare to another table

abc
abc/ab
abc/ab/123

I have worked out the other logic but an struggling with the various regexp.

Upvotes: 0

Views: 593

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94959

Here is the recursive query with SUBSTR and INSTR:

with cte(col) as
(
  select substr(col, 1, instr(col, '/', -1) - 1) from mytable
  union all
  select substr(col, 1, instr(col, '/', -1) - 1) from cte where instr(col, '/') > 0
)
select col from cte;

And here is the query with REGEXP_REPLACE:

with cte(col) as
(
  select regexp_replace(col, '/[^/]*$', '') from mytable
  union all
  select regexp_replace(col, '/[^/]*$', '') from cte where instr(col, '/') > 0
)
select col from cte;

Upvotes: 1

MT0
MT0

Reputation: 168061

You don't need a regular expression. You can do it with (faster) string functions:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE test_data ( id, value ) AS
  SELECT 1, 'abc/ab/123/zzz' FROM DUAL;

Query 1:

WITH bounds ( id, value, end_pos ) AS (
  SELECT id,
         value,
         INSTR( value, '/', 1 )
  FROM   test_data
  WHERE  INSTR( value, '/', 1 ) > 0
UNION ALL
  SELECT id,
         value,
         INSTR( value, '/', end_pos + 1 )
  FROM   bounds
  WHERE  INSTR( value, '/', end_pos + 1 ) > 0
)
SELECT id,
       SUBSTR( value, 1, end_pos ) AS item
FROM   bounds
ORDER BY id, end_pos

Results:

| ID |        ITEM |
|----|-------------|
|  1 |        abc/ |
|  1 |     abc/ab/ |
|  1 | abc/ab/123/ |

However, if you did want to use regular expressions then you could do:

Query 2:

WITH bounds ( id, value, lvl, item ) AS (
  SELECT id,
         value,
         1,
         REGEXP_SUBSTR( value, '.*?/', 1, 1 )
  FROM   test_data
  WHERE  REGEXP_SUBSTR( value, '.*?/', 1, 1 ) IS NOT NULL
UNION ALL
  SELECT id,
         value,
         lvl + 1,
         item || REGEXP_SUBSTR( value, '.*?/', 1, lvl + 1 )
  FROM   bounds
  WHERE  REGEXP_SUBSTR( value, '.*?/', 1, lvl + 1 ) IS NOT NULL
)
SELECT id,
       item
FROM   bounds

Results:

| ID |        ITEM |
|----|-------------|
|  1 |        abc/ |
|  1 |     abc/ab/ |
|  1 | abc/ab/123/ |

Upvotes: 1

Related Questions