CODEWITHSUNDEEP
sqloracle-databaseoracle9i
user1873196
user1873196

Reputation: 67

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

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.

Upvotes: 5

Views: 9222

Answers (2)

mrcaramori
mrcaramori

Reputation: 2503

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.

Upvotes: 5

Frank Schmitt
Frank Schmitt

Reputation: 30845

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.

Upvotes: 3

Related Questions