0

I am looking for a logic to remove all characters in a string except AD & Numeric values.

Examples

String Expected Result
AD-892839 statement AD-892839
BOD AD-89283 AD-89283
BOD AD-89283 ACGD AD-89283

I have tried to write a logic using regexp_replace but didn't get expected results.

In few cases we are receiving the transaction like AD-89823/1, AD-898232/2, AD-2283/5, etc.

A header Another header
AD-892839 statement AD-892839
BOD AD-89283 AD-89283
BOD AD-89283 ACGD AD-89283
BOD AD-89283/1,AD-89284/2,AD-89285/1 ACGD AD-89283/1,AD-89284/2,AD-89285/1
AD-89283/1,AD-89284/2,AD-89285/1 TIOL AD-89283/1,AD-89284/2,AD-89285/1
Improve this question

1 Answer 1

Reset to default
3 
with data as
(
  select 'AD-892839 statement' as text               from dual union all
  select 'BOD AD-89283'                              from dual union all
  select 'BOD AD-89283 ACGD'                         from dual union all
  select 'BOD AD-89283/1,AD-89284/2,AD-89285/1 ACGD' from dual union all
  select 'AD-89283/1,AD-89284/2,AD-89285/1 TIOL'     from dual
)
select regexp_substr(data.text, '(AD-([[:digit:]]|/)+|,)+')
from data;

REGEXP_SUBSTR(DATA.TEXT,'(AD-([[:DIGIT:]]
-----------------------------------------
AD-892839
AD-89283
AD-89283
AD-89283/1,AD-89284/2,AD-89285/1
AD-89283/1,AD-89284/2,AD-89285/1
Improve this answer
1
  • Hi Bala, I have tried with the given logic - regexp_substr(text, 'AD-([[:digit:]]|/|,)+') but it's not considering AD- for the second & third time. It's reterives only first time i.e AD-89283/1,
    – Madhu
    Commented Apr 17, 2023 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.