Regular expression won't work with special characters

[mdav2]

I have a string of text produced from a proprietary system that contains several values that I am interested in. Ideally I would have liked to be able to get this as individual field values however the suppliers say they can't produce this data. Ridiculous, I know but I can't do anything about that. Our systems support people for the system can't reproduce these totals so I am stuck with trying to use regular expressions to get the information needed.

That aside I have a couple of working regular expressions but I am stuck on getting one value back because it contains special characters.

I am looking for the value to the right of the string "Invoice Total (+ve)". I have a regular expression that gets the overall total (see below) that works and thought if I change the search text then it would work. It doesn't it just returns the entire data selected, with the spaces changed (as per the nested replace). Does anyone know if it is possible to do this with special characters?

This code works returning 694572.12:

SELECT REGEXP_REPLACE (REGEXP_REPLACE (payment_data, '( ){2,}', ' '),
                       '^.*Overall Total ([[:digit:]]+\.[[:digit:]]+).*$',
                       '\1',
                       1,
                       0,
                       'n'
                      )
          AS Overall_Total
  FROM data

This does not:

SELECT REGEXP_REPLACE (REGEXP_REPLACE (payment_data, '( ){2,}', ' '),
                       '^.*Invoice Total (+ve) ([[:digit:]]+\.[[:digit:]]+).*$',
                       '\1',
                       1,
                       0,
                       'n'
                      )
          AS Overall_Total
  FROM data

This is the data I working with:

Creditors Extract (Invoices) - WARWCRED_7347 on 06-12-2012

Number of batches                         1
Number of debit invoices                100
Number of credit invoices                 0
Number of transactions extracted      14440
Number of extract records               221

                             NET         VAT       GROSS
--------------------------------------------------------
Invoice Total (+ve)    694572.12        0.00   694572.12
Invoice Total (-ve)         0.00        0.00        0.00
Total Commitments      694592.88        0.00   694592.88
Total Contributions       -20.76         .00      -20.76
--------------------------------------------------------
Overall Total          694572.12        0.00   694572.12

Many Thanks in advance.

Mark Davies
Warwickshire County Council

 

[SantaMufasa]

Mark,

Which special character(s) seem not to work? Can you please post the code you are using to assign the special-character value(s)?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Thargy]

Could you post a sample of the strings you're parsing?

I'd like to see how regular their structure is and what hoops you're jumping through to extract what you need.

Regards

T

 

[karluk]

My testing indicates that you need to use the escape character before '(', '+' and ')'. The following code worked for me:

SELECT REGEXP_REPLACE (REGEXP_REPLACE (payment_data, '( ){2,}', ' '),
                       '^.*Invoice Total \(\+ve\) ([[:digit:]]+\.[[:digit:]]+).*$',
                       '\1',
                       1,
                       0,
                       'n'
                      )
          AS Invoice_Total
  FROM data