I have a table containing full body of water names in one column. ie:
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.
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.