INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Regular expression won't work with special characters

Regular expression won't work with special characters

(OP)
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:

CODE

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

This does not:

CODE

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:

CODE -->

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

RE: Regular expression won't work with special characters

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,

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

RE: Regular expression won't work with special characters

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

RE: Regular expression won't work with special characters

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

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close