Extract a number from column which contains a string-CodePudding

So i have a function, which returns a combination of strings (multiple values). I need to extract everything that is followed by char "DL:". But only that.

So before extraction:

**pck_import.GETdocnumber(XML_DATA)**
________________________________________
DL:2212200090001 Pr:8222046017
________________________________________
Obj:020220215541 DL:1099089729
________________________________________
DL:DST22017260
________________________________________
DL:22122000123964 Pr:8222062485
________________________________________
DL:22122000108599
________________________________________
Obj:0202200015539 DL:2100001688

In every case, i'll need the "number" after char "DL:". The "DL:" can be alone, can be at first place (between multiple values), also can be the last string. Also in some cases, the "DL:" value contains char, too.

So, output:

**OUTPUT**
______________
2212200090001
______________
1099089729
______________
DST22017260
______________
22122000123964
______________
22122000108599
______________
2100001688

I tried:

substr(pck_import.GETdocnumber(XML_DATA),
instr(pck_import.GETdocnumber(XML_DATA),
'DL:')   3))

That returns "Pr:", too.

CodePudding user response：

with s as (
select 'DL:2212200090001 Pr:8222046017'  str from dual union all
select 'Obj:020220215541 DL:1099089729'  str from dual union all
select 'DL:DST22017260'                  str from dual union all
select 'DL:22122000123964 Pr:8222062485' str from dual union all
select 'DL:22122000108599'               str from dual union all
select 'Obj:0202200015539 DL:2100001688' str from dual)
select str, regexp_substr(str, 'DL:(\S )', 1, 1, null, 1) rs
from s;

STR                             RS
------------------------------- -------------------------------
DL:2212200090001 Pr:8222046017  2212200090001
Obj:020220215541 DL:1099089729  1099089729
DL:DST22017260                  DST22017260
DL:22122000123964 Pr:8222062485 22122000123964
DL:22122000108599               22122000108599
Obj:0202200015539 DL:2100001688 2100001688

6 rows selected

CodePudding user response：

Something like this?

Sample data:

SQL> with test (col) as
  2    (select
  3  '________________________________________
  4  DL:2212200090001 Pr:8222046017
  5  ________________________________________
  6  Obj:020220215541 DL:1099089729
  7  ________________________________________
  8  DL:DST22017260
  9  ________________________________________
 10  DL:22122000123964 Pr:8222062485
 11  ________________________________________
 12  DL:22122000108599
 13  ________________________________________
 14  Obj:0202200015539 DL:2100001688'
 15  from dual)
 16  --

Query:

 17  select replace(regexp_substr(col, 'DL:\w ', 1, level), 'DL:') result
 18  from test
 19  connect by level <= regexp_count(col, 'DL:');

RESULT
--------------------------------------------------------------------------------
2212200090001
1099089729
DST22017260
22122000123964
22122000108599
2100001688

6 rows selected.

SQL>

(note that query might need to be modified if you'll be dealing with more than a single row of data)

CodePudding user response：

You could achieve this by using regular expressions utilising a positive lookbehind and lookahead.

The regex (?<=DL\:)\d*(?=\s)' matches all digits between DL: until a single whitespace character occurs.

You'd want to use the REGEXP_SUBSTR function for this (as you tagged this question with OracleSQL):

SELECT
  REGEXP_SUBSTR(my_column,
                '(?<=DL\:)\d*(?=\s)') "DL field"
  FROM my_table;

If you want to match substrings like DST22017260 as well, using . (any character) instead of \d would work: (?<=DL\:).*(?=\s).