notepad++ regular expression replace an inner sequence of matched text

Question

How would i replace all ocurances of "cast(net_price as decimal(9,2))=xxx AND" with "ROUND(net_price,2)=xxx AND" whilst maintaining xxx in Notepad++? The statements are nested with other AND statements as below:

What are the required find and replace parameters for notepad++?

Example text:

OR (cast(net_price as decimal(9,2))=@var2 AND cost=@var3 AND blah)

Example result:

OR (ROUND(net_price,2)=@var2 AND cost=@var3 AND blah)

Every time i try to write my own regex it seems to select the entire line from net_price up to the last AND , i want it to select up to the first AND after net_price, then continue on to the next one.

I had thought that net_price.*AND would match net_price then anything until it found AND. Plainly i am wrong and if someone could explain why i would appreciate it.

Answer 1

Apologies for the oversimplified question i have edited it to reflect the true more complicated question. Posting the answer in the hopes of giving back.

I should have pointed out that the sample data can be decimal(9,2) or float, or even decimal(9,4). so its much more complicated than just a search and replace. Sorry in trying to simplify for posting i oversimplified!!

It turned out that the correct reg is not .* as this is greedy match, I needed to use the .*? to match up to only the first AND statement after the net_price. As a greedy match will match to the last pattern it can find and then look backward to match the last AND. which would select far too much of the line - which is what my problem was.

So the regex becomes: cast\((net_price).*?\=(.*?) AND which:

finds cast( 
finds net_price and puts it into capture group 1
non greedy scans until it finds an equals character
captures non greedily the value into group 2
finds the AND

which captures into 2 groups net_price always in group 1 and the variable/quantity always in the group 2, everything else gets rewritten:

The replace becomes: ROUND\($1,2\)=$2 AND

Which replaces $1 (capture group 1) with net_price and $2 (capture group 2) with the value.

notepad++ search and replace window:

Answer 2

• Ctrl+H
• Find what: cast\((net_price) as decimal\(9,2\)\)(?==@var2 AND)
• Replace with: ROUND\($1,2\)
• CHECK Wrap around
• CHECK Regular expression
• Replace all

Explanation:

cast\(                  # literally
(net_price)             # group 1, "net_price"
as decimal\(9,2\)\)     # literally
(?==@var2 AND)          # positive lookahead, make sure we have "=@var2 AND" after

Replacement:

ROUND\($1,2\)

Screenshot (before):

Screenshot (after):