How to replace/remove multiple words from string in single statement

Question

I have a table containing full body of water names in one column. ie:

• Golden Lake
• Blue Water Lake
• Muskoka River
• Sandy Bay

I would like to select the water name, but not the type of water [lake, river, ect...]

Individually, I use:

select replace(watername, 'Lake') from water;

But there are 5 different water types that I was hoping to catch in a single select statement.

Answer 1

You can use a regex for the replace, using regexp_replace function, for example:

select regexp_replace('Golden Lake, Blue Water Lake, Muskoka River, Sandy B
ay', '( Lake)|( River)|( Bay)', '') from dual

Edit: Since it's 9i, you could try creating a function like this approach.

Or, you may end up doing some weird/ugly select.