asked on

Get a string replaced looking the pattern

I have an Oracle 11g table TB_Doc with a column DocNo ( and a few more columns)
Doc1Vol01/1
Doc2Vol119/2
Vol_Fred11/2
1-11-DocNormal/1
SomeDoc
DocFileNormal.pdf

I need to have a view in which I need to replace the word Vol and the volume number to nothing (Only if it is at the end)

So the above DocNos should come as
Doc1/1
Doc2/2
Vol_Fred11/2
1-11-DocNormal/1
SomeDoc
DocFileNormal.pdf

Ryan Chong

you may try this:

replace(yourField, regexp_substr(yourField, 'Vol.(\d*).[/]'), '/')

Open in new window

slightwv (䄆 Netminder)

I'm not sure the one above will catch everything.
For example: 'Doc1Voll01/1' and 'Doc1Vol1/1'

See if this finds everything:
regexp_replace(DocNo,'(\w)Vol\d+([/])','\1\2')

Sam OZ

ASKER

Hi,
Thanks. But both the above answers seems to be not working for me

These are real DocNos I have
TDR-010-E-00002VOL03ii/~
TDR-F180-G-12000VOL03-1/0
CDR-C123-M-0001VOL100/0
This should be converted in view as
TDR-010-E-00002/~
TDR-F180-G-12000/0
CDR-C123-M-0001/0

SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

any further assistance is needed here?

Ryan Chong

as suggested.