Removing part of a string out of a 'message'

Hello,
I have a string message coming back from a web service call and need to remove part of a string.
'Usage not authorized in dept<br>REV 9 not in bay 9'
I need to remove the '<br>REV 9 not in bay ' out of the message.
Sometimes this message can be by itself or within the message.

'Usage not authorized in dept<br>REV 9 not in bay 9'  should be 'Usage not authorized in dept'.
'REV 9 not in bay 9' should be null.
'Usage not authorized in dept<br>REV 9 not in bay 9<br>Request Follow up'  should be 'Usage not authorized in dept<br>Request Follow up'.

'REV 9 not in bay 9' will always be the same message or same value, so can search for this particular string and remove it.
Also, any '<br>' not needed or left alone, if another string is needed.

Much appreciated

Using Oracle 11g
MachinegunnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
regexp_replace(your_string,'(<br>)?REV 9 not in bay 9')

example usage...
SELECT s, REGEXP_REPLACE(s, '(<br>)?REV 9 not in bay 9')
  FROM (SELECT 'Usage not authorized in dept<br>REV 9 not in bay 9' s FROM DUAL
        UNION ALL
        SELECT 'REV 9 not in bay 9' s FROM DUAL
        UNION ALL
        SELECT 'Usage not authorized in dept<br>REV 9 not in bay 9<br>Request Follow up' s FROM DUAL)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MachinegunnerAuthor Commented:
Awesome, thanks for the quick reply.
I'll try it in the morning as I just signed off my work.
Thanks again.
MG
MachinegunnerAuthor Commented:
Good morning,
I tried the script and works great.  Thanks again for the help.
Have a good day.
MG
sdstuberCommented:
glad I could help, please remember to close the question then
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.