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.


Comments

Post a Comment

Popular posts from this blog

javascript - jQuery: Add class depending on URL in the best way -

i have html progress meter: <div class="col-md-3" style="margin-left: -20px;"> <div class="progress-pos active" id="progess-1"> <div class="progress-pos-inner"> login </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-2"> <div class="progress-pos-inner"> scan items </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-3"> <div class="progress-pos-inner"> confirm address </div> </div> </div> <div class="col-md-3 progress-pos-container"> <div class="progress-pos" id="progess-4"> <div class="progress-pos-in...
Read more

caching - How to check if a url path exists in the service worker cache -

i need check if particular url path exists in service worker cache. example, suppose url is: /myserviceworker/service?a=110&b=70 this url exists in cache, there many of them different values of , b. now, suppose want refresh of these urls, how can that? i want know how access key values service worker cache. if know key, plan follows: var url = new url(key); if(url.pathname === "\/myserviceworker/service") refetch key but not sure how cache key , in format is. mean, string or url? cache api has match() method returns promise resolving in response object if match or undefined if no match exists. second parameter object can specify ignoresearch not take account url parameters. the ignoresearch option supported firefox ( chrome status here ). in other hand, retrieve cache entries, can use keys() method.
Read more

Redirect to a HTTPS version using .htaccess -

i need modify .htaccess file redirect urls https version without "www". http://example.com --> https://example.com http://www.example.com --> https://example.com https://www.example.com --> https://example.com this how .htaccess file looks like: rewriteengine on rewritecond %{request_filename} !-d rewritecond %{request_filename} !-f rewriterule ^(.*)$ redirect.php?id=$1&page=$2 [qsa,l] what modifications need make .htaccess in order forward https without "www"? it depends on doing current code. looks file called redirect trying display php page seo friendly url. think might confusing people. anyway, need , force https without www, need 1 more rule above. also second rule needs two capture groups in rewriterule test string because wanting 2 different values using $1 , $2 references. rewriteengine on #rediect www non www and/or http https --- combinations. rewritecond %{http_host} !^example\.com [nc,or] rewritecond %{https} !^...
Read more