Regular Expression replacing substring

Question

It is available column like 'ABCURLCBSURLDMSURLWER' in a table. URL repeats in that column. I want to retrieve the statement between two URL like below.

Column
------
CBS
DMS

I wrote query such as below but the query I wrote wasn't retrieved the result that I wanted.

SELECT 
REGEXP_SUBSTR((SELECT REPLACE(REPLACE('ABCURLCBSURLDMSURLALI','URL',','),'ABC',',') AS AB FROM 
DUAL),'[^,]+',1,LEVEL) AS AB
FROM
DUAL
CONNECT BY
REGEXP_SUBSTR((SELECT REPLACE(REPLACE('ABCURLCBSURLDMSURLWER','URL',','),'ABC',',') AS AB FROM 
DUAL),'[^,]+',1,LEVEL)
IS NOT NULL;

AB
---
CBS
DMS
WER

How can i fix this query?

I had to use something repetitive to give an example, so I used it :)). — bcdyzi
– bcdyzi, Commented Sep 25, 2020 at 12:33

EJ Egyed · Accepted Answer · 2020-09-25 12:28:18Z

1

Try a query like this. Copying your solution to replace the URL text with a , makes the regex much simpler to split the string.

Updated Query

WITH some_data (ab) AS (SELECT 'ABCURLCBSURLDMSURLWER' FROM DUAL)
SELECT REGEXP_SUBSTR (REPLACE (sd.ab, 'URL', ','),
                      '[^,]+',
                      1,
                      lines.COLUMN_VALUE)    AS ab
  FROM some_data  sd,
       TABLE (CAST (MULTISET (    SELECT LEVEL     AS level_num
                                    FROM DUAL
                              CONNECT BY INSTR (sd.ab,
                                                'URL',
                                                1,
                                                LEVEL) > 0) AS SYS.odciNumberList)) lines
 WHERE lines.COLUMN_VALUE > 1;

Output

    AB
______
CBS
DMS

edited Sep 25, 2020 at 12:28

answered Sep 25, 2020 at 12:03

EJ Egyed

6,1341 gold badge11 silver badges25 bronze badges

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

6 Comments

bcdyzi Over a year ago

Unfortunately, The result is wrong. It retrieved ABC and WE as result of this query as well.

Pavel Smirnov Over a year ago

@EJEgyed, the OP wants values between URLs only, i.e. CBS and DMS

MT0 Over a year ago

This is wrong (apart from it doesn't do what the question asks) because [^(URL)] matches any one character that is not an opening round bracket ( or U or R or L or a closing round bracket ). So if you had the input as 'URL(LUR)(LUR)URL' then you would get no characters matched.

EJ Egyed Over a year ago

I have fixed the query so it should return proper results now.

MT0 Over a year ago

If the string is 'ABCURLCB,SURLDMSURLWER' this this only returns CB and S rather than CB,S and DMS. db<>fiddle

|

Littlefoot · Accepted Answer · 2020-09-25 12:33:15Z

1

Yet another option; see comments within code:

SQL> with test (col) as
  2    -- sample data
  3    (select 'ABCURLCBSURLDMSURLWER' from dual),
  4  rpl as
  5    -- replace URL with a semi-colon (a single/simple delimiter)
  6    (select replace(col, 'URL', ';') col
  7     from test
  8    ),
  9  rmv as
 10    -- remove everything in front of the 1st delimiter and everything after the last delimiter
 11    (select substr(col, instr(col, ';') + 1,
 12                        instr(col, ';', -1, 1) - instr(col, ';') - 1) val
 13     from rpl
 14    )
 15  select regexp_substr(val, '[^;]+', 1, level) result
 16  from rmv
 17  connect by level <= regexp_count(val, ';') + 1;

RESULT
--------------------
CBS
DMS

SQL>

answered Sep 25, 2020 at 12:33

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

2 Comments

MT0 Over a year ago

If the string is 'ABCURLCB;SURLDMSURLWER' then this returns 3 rows instead of 2. db<>fiddle

Littlefoot Over a year ago

Certainly, @MT0, but sample data says nothing about containing semi-colons within the string. On the other hand, it says nothing about NOT containing it either :)

MT0 · Accepted Answer · 2020-09-25 12:50:30Z

0

You don't need regular expressions and can do this with simple string functions:

WITH bounds ( id, value, start_pos, end_pos ) AS (
  SELECT id,
         value,
         INSTR( value, 'URL', 1, 1 ) + 3,
         INSTR( value, 'URL', 1, 2 )
  FROM   table_name
UNION ALL
  SELECT id,
         value,
         end_pos + 3,
         INSTR( value, 'URL', end_pos + 3, 1 )
  FROM   bounds
  WHERE  end_pos > 0
)
SELECT id,
       start_pos,
       SUBSTR( value, start_pos, end_pos - start_pos ) AS url
FROM   bounds
WHERE  end_pos > 0
ORDER BY id, start_pos;

So, for the sample data:

CREATE TABLE table_name ( id, value ) AS
SELECT 1, 'ABCURLCBSURLDMSURLWER' FROM DUAL UNION ALL
SELECT 2, 'ABCURLURLDEFURLGHIURL' FROM DUAL;

This outputs:

ID | START_POS | URL 
-: | --------: | :---
 1 |         7 | CBS 
 1 |        13 | DMS 
 2 |         7 | null
 2 |        10 | DEF 
 2 |        16 | GHI

db<>fiddle here

Option 2

If you did want to use regular expressions then you can use:

SELECT t.id,
       x.COLUMN_VALUE AS url
FROM   table_name t
       CROSS APPLY TABLE(
         CAST(
           MULTISET(
             SELECT REGEXP_SUBSTR(
                      t.value,
                      '(.*?)URL',
                      INSTR( t.value, 'URL' ) + 3,
                      LEVEL,
                      NULL,
                      1
                    )
              FROM  DUAL
              CONNECT BY
                     LEVEL <= REGEXP_COUNT(
                                t.value, '(.*?)URL', INSTR( t.value, 'URL' ) + 3
                              )
           )
           AS SYS.ODCIVARCHAR2LIST
         )
       ) x;

Which, for the same test data, outputs:

ID | URL 
-: | :---
 1 | CBS 
 1 | DMS 
 2 | null
 2 | DEF 
 2 | GHI

db<>fiddle here

edited Sep 25, 2020 at 12:50

answered Sep 25, 2020 at 12:10

MT0

173k12 gold badges70 silver badges136 bronze badges

2 Comments

EJ Egyed Over a year ago

Option 2 would not work if your test_data table had more than one row in it as you would get duplicated data.

MT0 Over a year ago

@EJEgyed The OP only had a single row of input. However, that's an easy fix so I've updated it to handle multiple rows. Just take the query and CROSS APPLY/CROSS JOIN it to the multi-row input table inside a table collection expression.

Collectives™ on Stack Overflow

Regular Expression replacing substring

3 Answers 3

Updated Query

Output

6 Comments

2 Comments

Option 2

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Updated Query

Output

6 Comments

2 Comments

Option 2

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related