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.

Xylia

Answer 1

Since you're using 9i, the simplest solution would be to use several REPLACEs:

with v_data as (
  select 'Golden Lake' name from dual union all
  select 'Blue Water Lake' name from dual union all
  select 'Muskoka River' name from dual union all
  select 'Sandy Bay' name from dual 
)  
select replace(
  replace(
    replace(name,
            ' Lake',
            ''),
    ' Bay',
    ''),
   ' River',
   '')
  from v_data

(I've added a space before each sub-pattern to avoid trailing spaces in the result).

Alternatively, you could try loading a Java class for Regular Expressions into the database and using that.

Particular

Thank you. I had used several replaces originally, but had not thought about trailing spaces. Too bad it couldn't have been as elegant as the regex solution. – Xylia

Answer 2

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.

Blasphemy

This looks like it would be perfect, however, I am on a 9i Database and this is an invalid identifier. Is there an equivilant for older Oracle DB's? – Xylia

@IHawk I'd suggest adding a space before each type (i.e. '( Lake)|( River)|( Bay)') - otherwise, the result will contain trailing spaces (which you usually don't want). – Particular

@Xylia You should have mentioned your version before (Oracle 9i is quite ancient, so usually you can safely assume at least 10g; regular expressions were added in 10g). Tagged oracle9i. – Particular