How would you replace a number followed by a question mark again followed by a number.

Input          : abc#95988287?3008009095
Desired Result : 95988287

I tried:

SELECT REGEXP_REPLACE('abc#95988287?3008009095', '\?[1,0,2,3,4,5,6,7,8,9]', '')
  FROM dual;

and the result was:

abc#95988287008009095

Please advice how to replace this.

CodePudding user response：

It seems that you want a substring, not a replacement. The following produces the result you're looking for:

select REGEXP_SUBSTR('abc#95988287?3008009095', '#(.*)\?', 1, 1, NULL, 1)
  from dual

db<>fiddle here

CodePudding user response：

Use \d for to match a digit and you need to escape the ? with \?:

SELECT REGEXP_REPLACE(
         'abc#95988287?3008009095',
         '\?\d'
       ) AS replaced_value
FROM   DUAL;

Which outputs:

REPLACED_VALUE
abc#95988287008009095

If you want to replace multiple digits then use \d to match one-or-more digits:

SELECT REGEXP_REPLACE(
         'abc#95988287?3008009095',
         '\?\d '
       ) AS replaced_value
FROM   DUAL;

Which outputs:

REPLACED_VALUE
abc#95988287

If you want to return the numeric substring between the # and the ? then you can use REGEXP_SUBSTR:

SELECT REGEXP_SUBSTR(
         'abc#95988287?3008009095',
         '#(\d )\?',
         1,
         1,
         NULL,
         1
       ) AS replaced_value
FROM   DUAL;

Or you can replace everything up to the first # and after the ? using:

SELECT REGEXP_REPLACE(
         'abc#95988287?3008009095',
         '.*?#|\?\d '
       ) AS replaced_value
FROM   DUAL;

Which both output:

REPLACED_VALUE
95988287

db<>fiddle here