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

user1873196 picture user1873196 · Jun 7, 2013 · Viewed 7.2k times · Source

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

mrcaramori picture mrcaramori · Jun 7, 2013

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.