CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsql
Rajiv A
Rajiv A

Reputation: 943

RegExp_Replace everything after a certain list of words in Oracle

I have a list of 200 +words in a column. I want to replace anything which comes after those words.

Eg: E Graham St       DDS  ==> E Graham St
    Trent Ave      4DF ===> Trent Ave

Examples of words are AVE, ST .... I am thinking of passing that string to a function and inside the function will have regexp replace.

Upvotes: 2

Views: 1450

Answers (1)

DazzaL
DazzaL

Reputation: 21973

you can use something like:

SQL> select regexp_replace(str, '^((.*? ave[ \.])|(.*? st[ \.])|(.*? rd[ \.])|(.*? close[ \.])).*$', '\1', 1, 1, 'i')
  2   from (select 'E Graham St       DDS' str from dual
  3         union all select 'Trent Ave      4DF' from dual
  4         union all select 'Foo bar Rd. asd' from dual
  5         union all select 'E Graham St St DDS' from dual);

REGEXP_REPLACE(STR,'^((.*?AVE[\.])|(.*?ST[\.])|(.*?RD[\.])|(.*?CLOSE[\.])).*$','
--------------------------------------------------------------------------------
E Graham St
Trent Ave
Foo bar Rd.
E Graham St

Upvotes: 4

Related Questions