replace function

[princessinthetower]

I'm trying to handle variations on the suffix Esq. in my database--Esq, Esq., ESQ, ESQ., Esquire. Are nested replaces the way to go or is there another function that deals with multiple conditions? I don't currently have any influence on getting the data cleaned up and consistent as quickly as it should be.

select replace(replace(pref_formatted_name,', ESQ',', Esquire.'),', Esq.',', Esquire')
from table

 

[SantaMufasa]

Princess,

Oracle's REPLACE function is the way that I would go to achieve your result.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Dagon]

I think there are dangers in using a nested function like that. For example, if you do:

select replace(
replace('John Smith Esq.', 'Esq.', 'Esquire'),
'Esq', 'Esquire')
from dual

you end up with 'John Smith Esquireuire'. There are also potentially problems if you are applying the function to the full name and the surname, for example, contains the letters 'Esq'.

I think a safer way to approach the problem would be to make sure the name is broken down into its component parts - prefix, first name, middle name, surname, suffix. Then you could just apply a decode (or case) to the suffix:

select decode('Esq.', 'Esquire', 'Esq', 'Esquire', 'ESQUIRE', 'Esquire',...)
from dual

 

[gunjan14]

Hi,
If you are using oracle 9i then you can use owa_patter.change for the formatting.
Steps:
1. Create a function to return the changed value
2. Call that function from select command

Here is the example

SQL> create or replace function replace_owa
  2  (
  3      p_str    in varchar2,
  4      p_to_chg in varchar2,
  5      p_chg    in varchar2
  6  ) return varchar2
  7  as
  8      l_chg_str varchar2(1000);
  9  begin
 10      l_chg_str := p_str;
 11      owa_pattern.change (l_chg_str, p_to_chg, p_chg);
 12      return l_chg_str;
 13  end;
 14  /

Function created.

Now you can get the replacement for esq/ESQ/EsqUIRE or any thing

SQL> select replace_owa([b]'to change ESQ. to something'[/b], 
  2                     '[Ee][Ss][Qq][^ ]*.\b', 
  3                     'Esquire '
  4                    ) a
  5  from   dual;

A
----------------------------------------
to change Esquire to something

SQL> select replace_owa([b]'to change ESQuire to something'[/b], 
  2                     '[Ee][Ss][Qq][^ ]*.\b', 
  3                     'Esquire '
  4                    ) a
  5  from   dual;

A
----------------------------------------
to change Esquire to something

SQL> select replace_owa([b]'to change ESQuire. to something'[/b], 
  2                     '[Ee][Ss][Qq][^ ]*.\b', 
  3                     'Esquire '
  4                    ) a
  5  from   dual;

A
----------------------------------------
to change Esquire to something

SQL> select replace_owa([b]'to change EsQuIrE to something'[/b], 
  2                     '[Ee][Ss][Qq][^ ]*.\b', 
  3                     'Esquire '
  4                    ) a
  5  from   dual;

A
----------------------------------------
to change Esquire to something

SQL> select replace_owa([b]'to change EsQ to something'[/b], 
  2                     '[Ee][Ss][Qq][^ ]*.\b', 
  3                     'Esquire '
  4                    ) a
  5  from   dual;

A
----------------------------------------
to change Esquire to something

This works on the pattern of regular expression. Let us know if this suffices your requirement

Regards,
Gunjan