Re: extract numeric part from address

Hello David,

  In case if number always located at the beginning of the filed and space delimited , you can use below:

select substr(address, 1, instr(address,' ',1) ) from <table>;

 If not, then you can use PL/SQL to scrub the address.

Regards,

  Ron
  DBA Infopower
  http://www.dbainfopower.com
  Standard disclaimer:
http://www.dbainfopower.com/dbaip_advice_disclaimer.html

"David Chang" <chang_dj_at_yahoo.com> wrote in message news:8a1Wb.209671$Rc4.1720145_at_attbi_s54...
> I am trying to do some analysis on customer's locality, so I'd like to
> extract numeric part from address.
>
> Here is how the table looks like
>
> Table Member
>
> first_name last_name address1 address2
> state zip
> ======= ======= =================== ===== === ===
> Tom whatever 1200 Evelyn Ave, #121
> CA 94102
>
> What I want to do is to write some sql to extract 1200 from Tom's
address1.
> Can any one give me some hint? Some sample code will be greatly
appreciated
> !!
>
> David
>
>
Received on Tue Feb 10 2004 - 10:50:04 CST