What is equivalent of ?= in Oracle regular expression

Question

I am trying to write a regular expression in regexp_substr().

For example, I have a string DA.*BLK and I want to extract whatever between A and B. What I am doing now is to use

replace(replace(regexp_substr(target_column,'A.*B'),'A',''),'B','')`

Then I get whatever is in between.

But I think this way may not be efficient and it's not elegant. I also tried to use ?= in there but it seems Oracle does not support such clause. Could any one please let me know a better way to achieve my purpose? Thank you very much!

Possible duplicate: https://stackoverflow.com/q/7758859/2422776 . Not voting to close as a duplicate so I won't dup-hammer by mistake. — Mureinik, Jul 09 '18 at 05:37
What result do you require from a string like `'AAA123BBB'`? — APC, Jul 09 '18 at 06:07
@APC Very good point, thank you! I think if I have that kind of problem I would be more specific in terms of the number of A or B. For example, if I need `123`, I would use `REGEXP_SUBSTR(AAA123BBB','AAA(.*)BBB', 1,1,NULL,1)` or I can use more neighboring characters to specify the boundary. But if you have better solutions please let me know. — noobie2023, Jul 09 '18 at 06:18

score 4 · Accepted Answer · answered Jul 09 '18 at 05:51

4

Use extract groups

REGEXP_SUBSTR('DASOMETHINGBLK','DA(.*)BLK', 1,1,NULL,1)
               --                           ^ ^      ^
               --starting from first, find first     1st match bet'n ()

http://sqlfiddle.com/#!4/97eade/967

answered Jul 09 '18 at 05:51

Kaushik Nayak

30,772
5
32
45

Definitely the neatest solution. However, note that this solution only works with the sixth parameter, subexpression, which was introduced in 11g. Also, it may not produce the expected result with a string like `'AAA123BBB'` – APC Jul 09 '18 at 06:06
@APC Thank you for your comment as well. It's a very good point! – noobie2023 Jul 09 '18 at 06:11

MT0 · Answer 2 · 2018-07-09T09:32:17.717

You do not need regular expressions, you can use the LIKE operator and string functions:

Oracle Setup:

CREATE TABLE table_name ( id, column_name ) AS
  SELECT 1, 'dablk' FROM DUAL UNION ALL
  SELECT 2, 'datestblk' FROM DUAL UNION ALL
  SELECT 3, 'atestblk' FROM DUAL UNION ALL
  SELECT 4, 'datestbl' FROM DUAL UNION ALL
  SELECT 5, 'dadatestblkblk' FROM DUAL;

Query - For strings starting with da and ending with blk:

SELECT id,
       SUBSTR( column_name, 3, LENGTH( column_name ) - 5 )
FROM   table_name
WHERE  column_name LIKE 'da%blk';

Results:

ID | SUBSTR(COLUMN_NAME,3,LENGTH(COLUMN_NAME)-5)
---|--------------------------------------------
 1 | (null)
 2 | test
 5 | datestblk

Query - For strings with a then b:

SELECT id,
       SUBSTR(
         column_name,
         first_a + 1,
         last_b - first_a - 1
       ) AS value
FROM   (
  SELECT t.*,
         INSTR( column_name, 'a' ) AS first_a,
         INSTR( column_name, 'b', -1 ) AS last_b
  FROM   table_name t
  WHERE  column_name LIKE '%a%b%'
)

Results:

ID | VALUE    
---|----------
 1 | (null)
 2 | test     
 3 | test     
 4 | test     
 5 | datestblk

db<>fiddle here

What is equivalent of ?= in Oracle regular expression

2 Answers2