how to cut data from in Oracle's SQL-CodePudding

i have a table with data and particular column where i need to cut data e.g.:

on unknown id:138702 client_type:0 contract:25369/NBX1/010322

so i need to cut only 138702

i tried to use SUBSTR but the thing is that besides the example above there is also:

 on CALL3 id:138702 client_type:0 contract:25369/NBX1/010322
 on SOPP6 id:138702 client_type:16 contract:25369/JMZXH3/010322

so results are kinda messy.

id appreciate if someone could help me with this.

CodePudding user response：

One option uses REGEXP_REPLACE() function such as

SELECT REGEXP_REPLACE(col, '(.*id:)(\d ).*','\2') AS id
  FROM t

which finds the digits just after id: upto the digits end by operator which matches all occurences for the digits where whole pattern divides the strings into two parts.The first part starts from the beginning upto id:, and the rest(\2) is id value

Demo

CodePudding user response：

If you're looking for a first numeric value that follows the id: string, then this might be one option:

Sample data:

SQL> with test (col) as
  2    (select 'on unknown id:138702 client_type:0 contract:25369/NBX1/010322'  from dual union all
  3     select 'on CALL3 id:138702 client_type:0 contract:25369/NBX1/010322'    from dual union all
  4     select 'on SOPP6 id:138702 client_type:16 contract:25369/JMZXH3/010322' from dual
  5    )

Query begins here:

  6  select col,
  7    ltrim(regexp_substr(col, 'id:\d '), 'id:') result
  8  from test;

COL                                                            RESULT
-------------------------------------------------------------- ----------
on unknown id:138702 client_type:0 contract:25369/NBX1/010322  138702
on CALL3 id:138702 client_type:0 contract:25369/NBX1/010322    138702
on SOPP6 id:138702 client_type:16 contract:25369/JMZXH3/010322 138702

SQL>