Regexp_replace

*** Please note I have stumbled across a better, i think most correct way, of accomplishing the below, but using REGEXP_SUBSTR here.

Just a reminder when using PL/SQL regexp_replace -

Your search string can be used as a 'filter' to get exactly what you want out of the string and nothing more.

Consider the following: your string is the following: BUTTE 211 - Color (BSS) or BUTTE 211 - B&W (BSS) or in some cases BUTTE 211 - random words.

You need to extract *only* the words between 'BUTTE 211 - ' and the parenthesized items, if they exist '(BSS)'. Sometimes, on rare occasions, words exist other than Color & B&W in the string between these two expressions.

You could write multiple regexp functions nested, or even do an case when instr('Color') > 0 but

the following does the same thing, IN ONE STATEMENT/Function call:

regexp_replace(q.queuename, '(.* - | \(.*\))', '\2')

Basically what the above expression is doing is telling the regexp_replace function to get either .* - or (.*) into backreference \1. Since we don't want those characters and specifically anything but those expressions, we ask for \2...anything *not* those strings.

Viola: you get your answer, Color and B&W (and in some cases, 'random word')