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

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?


sikyalaSenior Database AdministratorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
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
 
sdstuberCommented:
if the rule for extracting the piece you want is something else, please specify and I'll adjust accordingly
0
 
sikyalaSenior Database AdministratorAuthor Commented:
I basically want to exclude RDBAFM_ORA10://cpia_lib/
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
sdstuberConnect With a Mentor Commented:
that's even simpler

REPLACE(dc_rule, 'RDBAFM_ORA10://cpia_lib/')
0
 
sikyalaSenior Database AdministratorAuthor Commented:
sdstuber: exactly it worked. Thanks
0
 
sikyalaSenior Database AdministratorAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.