Home » SQL & PL/SQL » SQL & PL/SQL » How to write down regular expression for this (Oracle10g)
How to write down regular expression for this [message #396658] Tue, 07 April 2009 08:45 Go to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi,

To Mr/Mrs $V_CUSTOMERNAME$
Subject: Reminder- Promise to pay
Dear Sir,
This is to remind you that you have agreed to pay $V_LM_LOAN_CURR_DESC$ $V_PROMISE_AMOUNT$ by $V_PROMISE_DATE$ based on the past commitment.
Regards,
Collections Manager

My text is like this as shown above
I have to extract the name start with $ and with $ .
Ex .$V_CUSTOMERNAME$. It might be mulitple times can you provide me regular expression for that.


Re: How to write down regular expression for this [message #396662 is a reply to message #396658] Tue, 07 April 2009 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why regular expressions? You just want to replace $V_CUSTOMERNAME$ by a string, $V_LM_LOAN_CURR_DESC$ by another one and so on.

Regards
Michel
Re: How to write down regular expression for this [message #396664 is a reply to message #396658] Tue, 07 April 2009 09:12 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

NOt like this.

I have to extract all the text that is included in '$text$' like this.
Re: How to write down regular expression for this [message #396665 is a reply to message #396658] Tue, 07 April 2009 09:13 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Please, please, please Read & Follow Posting Guidelines above.
Go to the section labeled "Practice" & do as directed.


Post DDL for tables.
Post DML for test data.

Post expected/desired results.
Re: How to write down regular expression for this [message #396667 is a reply to message #396658] Tue, 07 April 2009 09:25 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

MY text is like this in the table

"This is to remind you that Mr/Mrs: $V_CUSTOMERNAME$ has promised to pay $V_LM_LOAN_CUR$ on $V_DUE_DATE$ against loan No: $V_LOANNO$

System ".

I have to extract those names included in start with '$'and end with '$'.
Ex .$V_CUSTOMERNAME$,
$V_LM_LOAN_CUR$ ,
$V_DUE_DATE$,
$V_LOANNO$
with comma seperated values

Please suggest me some idea

Re: How to write down regular expression for this [message #396673 is a reply to message #396667] Tue, 07 April 2009 09:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect Michel is right,and that what you actually want to do is to replace the strings with values, but I'll give you the benefit of the doubt.

Something like this should do it:
with src as (select 'This is to remind you that Mr/Mrs: $V_CUSTOMERNAME$ has promised to pay $V_LM_LOAN_CUR$ on $V_DUE_DATE$ against loan No: $V_LOANNO$ '||
                    chr(10)|| 
                    'System ' col_1 from dual)
 select lvl,regexp_substr(col_1,'\$[^$]+\$',1,lvl)
 from   src
       ,(select level lvl from dual connect by level <= 5);
Re: How to write down regular expression for this [message #396746 is a reply to message #396658] Tue, 07 April 2009 23:11 Go to previous messageGo to next message
sr_orcl
Messages: 82
Registered: January 2009
Location: mumbai
Member

Hi ,
But why you are given 5 in connect by clause.the count of the variable is not constant in all the text field.It might be varies as per the text store in the table.

Re: How to write down regular expression for this [message #396771 is a reply to message #396746] Wed, 08 April 2009 00:52 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Change 5 to 1000 if you want.
Do you think there can be more than 1000 values?
Then change 1000 to 10000...

Regards
Michel
Previous Topic: Diff b/w drop and unused
Next Topic: Insert into select clause is taking long time than the select [merged]
Goto Forum:
  


Current Time: Fri Dec 09 21:05:41 CST 2016

Total time taken to generate the page: 0.06004 seconds