Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 723
  • Last Modified:

Extracting tokens from a string using substr function.

Tokens come within #..#, and are not fixed, could be any number.

Program should extract tokens one by one and assign it into variable.
2 Solutions
gram77Author Commented:

sample string
This report is ran on #sysdate#, The report is run on #as_of_dt#. Report is written on file #name#
Assuming name adn as_of_dt are variables
select regexp_replace( regexp_replace(regexp_replace('This report is ran on #sysdate#, The report is run on #as_of_dt#. Report is written on file #name#','#sysdate#', sysdate),
                                                                     '#name#', name),
                                  '#as_of_dt#',as_of_dt) from dual

If as_of_dt,name are column name then replace dual with table name.
slightwv (䄆 Netminder) Commented:
So if I read this right, you want to extract say #sysdate# then assign that to a pl/sql variable like:    #sysdate# := 'some value'

If so, sdstuber already provided the main piece of the code in:

I provided the loop to loop through the delimited strings in the same question:

Please confirm the exact requirements and we'll see if we can provide working code.
for this we have written a procedure.  

Attached is the procedure and other file is calling of the procedure

HTH parse-list.sql calling-pk-parse-list.sql

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now