oracle - Regex to find 9 to 11 digit integer occuring anywhere closest to a keyword -

in simple term, looking if there string, has keyword ztfn00, regex shall able return closest 9 11 digit number left or right side of string.

i want in regexp_replace function of oracle.

below of sample strings:

the following error occurred in sap update_bp service part of combine:   (error:653, r11:186:number 867278489 exists id type ztfn00) 

expected result: 867278489

the following error occurred in sap update_bp service part of combine  (error:653, r11:186:number ztfn00 identification number 123456778 exist) 

expected result: 123456778

i not find way regular expressions, if want task without pl/sql, can following.

it's little bit tricky, combining many calls regexp functions evaluate, each occurrence of digit string, distance keyword , pick nearest one.

with test(string, keyword)  ( select '(error:653, r11:186: 999999999 number 0000000000 exists id type ztfn00  hjhk  11111111111 kjh k222222222)', 'ztfn00' dual) select numberstring (     select numberstring,             decode (greatest (numberposition, keywordposition),                       keywordposition,                         keywordposition - numberposition - numberlength,                       numberposition,                         numberposition - keywordposition - keywordlength                   ) distance     (             select regexp_instr(string, '[0-9]{9,11}', 1, level) numberposition,                    instr( string, keyword) keywordposition,                    length(regexp_substr(string, '[0-9]{9,11}', 1, level)) numberlength,                    regexp_substr(string, '[0-9]{9,11}', 1, level) numberstring,                    length(keyword) keywordlength             test             connect regexp_instr(string, '[0-9]{9,11}', 1, level) != 0          )     order distance )where rownum = 1      

looking @ single parts:

sql> test(string, keyword)   2  ( select   3  '(error:653, r11:186: 999999999 number 0000000000 exists id type ztfn00  hjhk  11111111111 kjh k222222222)',   4  'ztfn00'   5  dual)   6              select regexp_instr(string, '[0-9]{9,11}', 1, level) numberposition,   7                     instr( string, keyword) keywordposition,   8                     length(regexp_substr(string, '[0-9]{9,11}', 1, level)) numberlength,   9                     regexp_substr(string, '[0-9]{9,11}', 1, level) numberstring,  10                     length(keyword) keywordlength  11              test  12              connect regexp_instr(string, '[0-9]{9,11}', 1, level) != 0;  numberposition keywordposition numberlength numberstring     keywordlength -------------- --------------- ------------ ---------------- -------------             22              77            9 999999999                    6             39              77           10 0000000000                   6             91              77           11 11111111111                  6            108              77            9 222222222                    6 

this scans string, , iterates while insrt (...) != 0, while there occurrences; level used first, second, ... occurrence, row 1 gives first occurrence, row 2 second , on, while exists nth occurrence.

this part used evaluate useful fields, tha use both right , left of keyword, evaluating distance between string number , keyword:

select numberstring,             decode (greatest (numberposition, keywordposition),                       keywordposition,                         keywordposition - numberposition - numberlength,                       numberposition,                         numberposition - keywordposition - keywordlength                   ) distance 

the inner query ordered distance, first row contains nearest string; that's why in outermost query extract row rownum = 1 nearest row. can re-written in more compact way, bit more readable.

this should work when have multiple occurrences of digit string, on both sides of keyword.