Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

what is syntax for stripping characters from a field value and leaving the remaining to query against another field

Posted on 2011-02-25
6
Medium Priority
?
332 Views
Last Modified: 2012-05-11
I need to get the latter characters from a field and compare it with the field value in another table. What is the syntax to strip the characters that I don't need and use only the characters I do need in a query?

This is what I have:

select count(*) from dc_rule
where Doc_I in
(select pdf from holdings
  where pub_date >='01-JAN-10'
              and pub_date<='31-DEC-10');

dc_rule field has the following value:

RDBAFM_ORA10://cpia_lib/CPIA-1995-0525

I don't want to remove any characters. I just want to capture a portion of the value of the field and compare it to another field. For example I only want CPIA-1995-0525 in the above sample. Is that possible? If so How would I do this?


0
Comment
Question by:sikyala
  • 3
  • 3
6 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 34982287
parsing is all about rules, you haven't specified how we identify which piece of the string you would want.

So I'll take a guess.

You want the substring following the last "/"  character.

try this...


regexp_substr(dc_rule,'[^/]+$')
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 34982308
if the rule for extracting the piece you want is something else, please specify and I'll adjust accordingly
0
 

Author Comment

by:sikyala
ID: 34982317
I basically want to exclude RDBAFM_ORA10://cpia_lib/
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 34982346
that's even simpler

REPLACE(dc_rule, 'RDBAFM_ORA10://cpia_lib/')
0
 

Author Comment

by:sikyala
ID: 34982350
sdstuber: exactly it worked. Thanks
0
 

Author Closing Comment

by:sikyala
ID: 34982542
Thanks
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month13 days, 5 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question