How to return a numeric substring given exceptions?-CodePudding

It is necessary to pull out numerical sequences consisting of 5 or 6 digits, excluding numeric sequences containing sequences of letters on the left side 'CR', 'MRLID_', 'GEO_'. The first hyphen is not a search key

My example:

SELECT REGEXP_SUBSTR('84830-Soc_Dem-Carousel-CR236666',
                     '([^CR\d{6}] )|([^MRLID_\d{5}] )|([GEO_\d{5}] )\d{5,6}',
                     1,
                     1,
                     'i')
  FROM dual

If input string has the following form:

'McCombo_Mar-Apr19_mcd_installs;759678/;CR759428-Soc_Dem-Multi_roll_15sec-R27?<MRLID_12345>%GEO_78934?]ysl_fraw_blackopium_display_aw'

It is necessary to pull out the value 759678. This is a numeric sequence, it can consist of 5 or 6 characters, it can be located anywhere in the line, it is not possible to somehow select it with a separator.

CodePudding user response：

Use regexp_replace instead to replace -.* with blank:

select regexp_replace('84830-Soc_Dem-Carousel-CR236666', '-.*','') from dual

See live demo.

CodePudding user response：

How about this?

SQL> with test (col) as
  2    (select 'McCombo_Mar-Apr19_mcd_installs;759678/;CR759428-Soc_Dem-Multi_roll_15sec-R27?<MRLID_12345>%GEO_78934?]ysl_fraw_blackopium_display_aw' from dual)
  3  select val
  4  from (select regexp_substr(col, '[[:alnum:]_] ', 1, level) val
  5        from test
  6        connect by level <= regexp_count(col, '[[:alnum:]_] ')
  7       )
  8  where regexp_like(val, '^\d $');

VAL
--------------------------------------------------------------------------------
759678

SQL>

subquery returns all alphanumeric substrings (that's regexp_substr)
the main query returns values that consist of numbers only (that's regexp_like)