Replace first occurence of string from the right 1

[oradba101]

Greetings,

I need to be able to replace street names with their abbreviations. For example,

123 Maine Street would become 123 Main St

If I use the REPLACE function it replaces all occurrences. For example,

123 Lanes Lane would become 123 Lns Ln

Any help would be greatly appreciated.

Regards,



William Chadbourne
Programmer/Analyst

 

[SantaMufasa]

William,

Can you post a comprehensive list of the road types that you are trying to replace plus their replacements? If so, I believe we have a solution for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Actually, I don't need the comprehensive list...Here is code that should do what you want. Notice that if an address is already abbreviated, or if there is no street type, it is not a problem...the code just returns the address without modification:

select * from addresses;

ID STREET_ADDR
-- ----------------------
 1 123 Streeter Street
 2 1234 Laney Lane
 3 1234 Coverdale
 5 1234 Heather Rd
 4 1234 High Road
 6 1234 Holly Place
 7 1234 Wasatch Boulevard
 8 1234 Circle Circle
 9 1234 Ocean Cove
10 1234 Court Court
11 1234 Driver Drive
12 1234 Waverly Way
13 1234 Pirates Passage

select id
      ,substr(street_addr,1,instr(street_addr,' ',-1))
     ||decode(upper(substr(street_addr,instr(street_addr,' ',-1)+1))
             ,'STREET','ST'
             ,'LANE','LN'
             ,'ROAD','RD'
             ,'PLACE','PL'
             ,'BOULEVARD','BLVD'
             ,'CIRCLE','CIR'
             ,'COVE','CV'
             ,'COURT','CT'
             ,'DRIVE','DR'
             ,'WAY','WY'
             ,substr(street_addr,instr(street_addr,' ',-1)+1)
             ) STREET_ADDR
   from addresses
/

ID STREET_ADDR
-- --------------------
 1 123 Streeter ST
 2 1234 Laney LN
 3 1234 Coverdale
 5 1234 Heather Rd
 4 1234 High RD
 6 1234 Holly PL
 7 1234 Wasatch BLVD
 8 1234 Circle CIR
 9 1234 Ocean CV
10 1234 Court CT
11 1234 Driver DR
12 1234 Waverly WY
13 1234 Pirates Passage

If there are street types that I have forgotten, you can simply add them into the DECODE list.

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[oradba101]

Hi, Dave

Perhaps I should have been a little more specific. I need a function to do this.

It actually runs from an Oracle form. When the user types the address in and let's say that they type in "Lane" as soon as they exit the form the "Lane" is converted to "Ln".

Thanks,


William Chadbourne
Programmer/Analyst

 

[SantaMufasa]

William,

Yes, it would have avoided some time waste on my part to know that a function is what you wanted <grin>.

Here, then, is both a function definition and the proof-of-concept query using the function (based on the same data as before):

create or replace function street_abbrev (addr_in varchar2) return varchar2 is
    hold_addr varchar2(2000);
    tail varchar2(2000);
    abbr varchar2(10);
begin
    hold_addr := substr(addr_in,1,instr(addr_in,' ',-1));
    tail := upper(substr(addr_in,instr(addr_in,' ',-1)+1));
    if tail = 'STREET' then abbr := 'ST';
    elsif tail = 'LANE' then abbr := 'LANE';
    elsif tail = 'ROAD' then abbr := 'RD';
    elsif tail = 'PLACE' then abbr := 'PL';
    elsif tail = 'BOULEVARD' then abbr := 'BLVD';
    elsif tail = 'CIRCLE' then abbr := 'CIR';
    elsif tail = 'COVE' then abbr := 'CV';
    elsif tail = 'COURT' then abbr := 'CT';
    elsif tail = 'DRIVE' then abbr := 'DR';
    elsif tail = 'WAY' then abbr := 'WY';
    else  abbr := tail;
    end if;
    return hold_addr||initcap(abbr);
end;
/

col address format a30
select id, street_abbrev (street_addr) address from addresses;

ID ADDRESS
-- --------------------
 1 123 Streeter St
 2 1234 Laney Lane
 3 1234 Coverdale
 5 1234 Heather Rd
 4 1234 High Rd
 6 1234 Holly Pl
 7 1234 Wasatch Blvd
 8 1234 Circle Cir
 9 1234 Ocean Cv
10 1234 Court Ct
11 1234 Driver Dr
12 1234 Waverly Wy
13 1234 Pirates Passage

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Oops...my code

...'LANE' then abbr := 'LANE';...

...should obviously have read:

'LANE' then abbr := 'LN';

Sorry...it must be the end of the day.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[oradba101]

Hi, Dave

Looks like exactly what I need

Thanks,



William Chadbourne
Programmer/Analyst