Home » SQL & PL/SQL » SQL & PL/SQL » regexp_replace function (10g)
regexp_replace function [message #465536] Wed, 14 July 2010 10:35 Go to next message
brian kendall
Messages: 6
Registered: July 2010
Junior Member
Hello all. I've been tasked to parse tags from a string that look like the following:

{Date + XXX}

where XXX represents a numeric value. I have to replace this, including the brace characters with

SYSDATE + XXX

which will ultimately calculate SYSDATE plus the number of days suggested by XXX. The problem is that I am trying to use regexp_replace to achieve this goal but since XXX is completely arbitrary, I cannot search for it as a fixed value. So, ultimately, I would like to use a regular expression that ignores the numeric part of my search and only replaces the starting brace, the "Date + " part and the ending brace, leaving the numeric portion intact. Is there a way to do this?

I was trying to do something like the following
myString := regexp_replace(myString, '\{(Date \+ [^[::digit:]]{1,})\}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));

in hopes of making it ignore the numeric part but it, instead, treats occurrences as a non match. Alternatively, the call below

myString := regexp_replace(myString, '\{(Date \+ [[:digit:]{1,})\}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));

matches correctly but replaces the numeric portion as well, so I'm left with just today's date instead of the calculated future date...

[Updated on: Wed, 14 July 2010 10:40]

Report message to a moderator

Re: regexp_replace function [message #465537 is a reply to message #465536] Wed, 14 July 2010 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
replace(replace(<string>, '{Date', 'SYSDATE'),'}','')

Regards
Michel

[Updated on: Wed, 14 July 2010 10:38]

Report message to a moderator

Re: regexp_replace function [message #465539 is a reply to message #465537] Wed, 14 July 2010 10:55 Go to previous messageGo to next message
brian kendall
Messages: 6
Registered: July 2010
Junior Member
Thanks for the quick response! I tried it and it's almost but not quite it. That removes the braces and replaces "Date" with "SYSDATE" but it still won't allow for the calculation part... So I end up with

July 14, 2010 + XXX

Any ideas?
Re: regexp_replace function [message #465540 is a reply to message #465539] Wed, 14 July 2010 10:57 Go to previous messageGo to next message
brian kendall
Messages: 6
Registered: July 2010
Junior Member
Also, I forgot to mention that this has to work for multiple occurrences of the {Date + XXX} in the same string...
Re: regexp_replace function [message #465542 is a reply to message #465540] Wed, 14 July 2010 11:13 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data.

Before, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Wed, 14 July 2010 11:13]

Report message to a moderator

Re: regexp_replace function [message #465544 is a reply to message #465542] Wed, 14 July 2010 11:42 Go to previous messageGo to next message
brian kendall
Messages: 6
Registered: July 2010
Junior Member
Hi. Sorry for that. First time on the forum. I don't have create table and insert statements available. I'm doing this in an anonymous block as follows. Hopefully this will be acceptable? In the preview, it looks like it adds a URL to this site's wiki but I don't know how to make it remove it... Thanks!

-Brian

DECLARE
  message_txt NVARCHAR2(32000); 
BEGIN
 
  message_txt := 
'Mywholemessageemailwhatever...asf;lkjaslkfj;lwejr{Date + 533}lkasdjfl;j;la{Date + 322}kjsdflj;las{Date}jdf';
--message text
 
  dbms_output.put_line('Original string before the replacement is: ' || message_txt || '.');
---print the original message
 
  message_txt := replace(replace(message_txt, '{Date','SYSDATE'),'}','');
  message_txt := replace(message_txt, 'SYSDATE',to_char(SYSDATE,'FMMonth DD, YYYY'));
  message_txt := 
regexp_replace(message_txt, 
               'SYSDATE \+ ([url=http://www.orafaq.com/wiki/:digit:]:digit:[/url]{1,})', 
               to_char(SYSDATE,'FMMonth DD, YYYY'));
   
  dbms_output.put_line('Final string after replacement is: ' || message_txt || '.'); 
--final output message
 
END;

[Updated on: Wed, 14 July 2010 12:00] by Moderator

Report message to a moderator

Re: regexp_replace function [message #465546 is a reply to message #465544] Wed, 14 July 2010 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What should be the result of your text example?

Regards
Michel
Re: regexp_replace function [message #465553 is a reply to message #465546] Wed, 14 July 2010 13:24 Go to previous messageGo to next message
brian kendall
Messages: 6
Registered: July 2010
Junior Member
Changing the string for simplicity, the result for the following:

Mywholemessageemailwhatever...asf;lkjaslkfj;lwejr{Date + 5}lkasdjfl;j;la{Date + 3}kjsdflj;las{Date}jdf
Mywholemessageemailwhatever...asf;lkjaslkfj;lwejrJuly 19, 2010lkasdjfl;j;laJuly 17, 2010kjsdflj;las{July 14, 2010}jdf

As you see above, I add only 5, 3 and 0 days to the 3 Date areas. In the code above, it was adding 533, 22 and 0 days do the Date areas... Thanks again for your help!

-Brian
Re: regexp_replace function [message #465558 is a reply to message #465553] Wed, 14 July 2010 14:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
Something like:

DECLARE
  message_txt NVARCHAR2(32000);
  offset number;
  replace_what varchar2(200);
  replace_with varchar2(300);
BEGIN
  message_txt := 'Mywholemessageemailwhatever...asf;lkjaslkfj;lwejr{Date + 533}lkasdjfl;j;la{Date + 322}kjsdflj;las{Date}jdf';
--message text
 
  dbms_output.put_line('Original string before the replacement is: ' || message_txt || '.');
---print the original message

  loop
    replace_what := regexp_substr(message_txt,'\{Date.*?\}');
dbms_output.put_line('replace_what = ' || replace_what);
    exit when replace_what is null;
dbms_output.put_line('replace_with = ' || to_char(sysdate + nvl(regexp_substr(replace_what,'\d+'),0),'FMMonth DD, YYYY'));
    replace_with := to_char(sysdate + nvl(regexp_substr(replace_what,'\d+'),0),'FMMonth DD, YYYY');
    message_txt := regexp_replace(message_txt,'\{Date.*?\}',replace_with,1,1);
  end loop;
   
  dbms_output.put_line('Final string after replacement is: ' || message_txt || '.'); 
--final output message
END;
/
Original string before the replacement is: Mywholemessageemailwhatever...asf;lkjaslkfj;lwejr{Date + 533}lkasdjfl;j;la{Date + 322}kjsdflj;las{Date}jdf.
replace_what = {Date + 533}
replace_with = December 29, 2011
replace_what = {Date + 322}
replace_with = June 1, 2011
replace_what = {Date}
replace_with = July 14, 2010
replace_what =
Final string after replacement is: Mywholemessageemailwhatever...asf;lkjaslkfj;lwejrDecember 29, 2011lkasdjfl;j;laJune 1, 2011kjsdflj;lasJuly 14, 2010jdf.

PL/SQL procedure successfully completed.

SQL> 


SY.
Re: regexp_replace function [message #465559 is a reply to message #465553] Wed, 14 July 2010 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> declare 
  2    val varchar2(300) :=
  3      'Mywholemessageemailwhatever...asf;lkjaslkfj;lwejr{Date + 5}lkasdjfl;j;la{Date + 3}kjsdflj;las{Date}jdf';
  4  begin
  5    val := ''''||val||'''';
  6    val := replace(val, '{Date', '''||TO_CHAR(SYSDATE');
  7    val := replace(val, '}', ',''FMMonth DD, YYYY'')||''');
  8    execute immediate 'begin :r := '||val||'; end;' using out val;
  9    dbms_output.put_line(val);
 10  end;
 11  /
Mywholemessageemailwhatever...asf;lkjaslkfj;lwejrJuly 19, 2010lkasdjfl;j;laJuly 17, 2010kjsdflj;lasJuly 14, 2010jdf

Regards
Michel

[Updated on: Wed, 14 July 2010 14:18]

Report message to a moderator

Re: regexp_replace function [message #465561 is a reply to message #465559] Wed, 14 July 2010 14:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
Michel

I thought about it, but:

val := replace(val, '}', ',''FMMonth DD, YYYY'')||''');

implies } is not used anywhere else in the string.

SY.
Re: regexp_replace function [message #465562 is a reply to message #465561] Wed, 14 July 2010 14:38 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2086
Registered: January 2010
Senior Member
I took a liberty to modify your solution with regexp:

declare
    val varchar2(300) := 'Mywholemessageemailwhatever...asf;lkjaslkfj;lwejr{Date + 5}lkasdjfl;j;la{Date + 3}kjsdflj;las{Date}jdf';
  begin
    val := ''''||val||'''';
    val := regexp_replace(val,'\{Date(.*?)\}', '''||TO_CHAR(SYSDATE\1,''FMMonth DD, YYYY'')||''');
    execute immediate 'begin :r := '||val||'; end;' using out val;
    dbms_output.put_line(val);
end;
/
Mywholemessageemailwhatever...asf;lkjaslkfj;lwejrJuly 19, 2010lkasdjfl;j;laJuly 17, 2010kjsdflj;lasJuly 14, 2010jdf



SY.
Re: regexp_replace function [message #465563 is a reply to message #465562] Wed, 14 July 2010 14:41 Go to previous messageGo to next message
Michel Cadot
Messages: 60062
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I took a liberty to modify your solution with regexp:

You're welcome. Smile

Regards
Michel
Re: regexp_replace function [message #465564 is a reply to message #465563] Wed, 14 July 2010 14:48 Go to previous messageGo to next message
brian kendall
Messages: 6
Registered: July 2010
Junior Member
Wow, these solutions are fantastic! Thanks folks for your help! It is MUCH appreciated! Now I know where to go for ANY Oracle questions I have!

-Brian
Re: regexp_replace function [message #465583 is a reply to message #465559] Wed, 14 July 2010 22:00 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Cool idea Michel !
Previous Topic: Split the values from column
Next Topic: Aggregator between two dates
Goto Forum:
  


Current Time: Fri Dec 26 11:15:03 CST 2014

Total time taken to generate the page: 0.10517 seconds