Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> Re: extract numeric part from address

Re: extract numeric part from address

From: UNIXNewBie <nospan_at_nospam.com>
Date: Thu, 19 Feb 2004 23:22:20 -0500
Message-ID: <7Iadnd0iwokcFqjdRVn-hw@magma.ca>


Assuming we only have 1 space between the street number and the name of the street I would try this

SELECT SUBSTR(ADDRESS, INSTR(ADDRESS,' ',1)) FROM <TABLE>

The examle below would return the number part.

If you wanted to be sure you might have to resort to PL/SQL if there are more that 1 space.

J.

<sybrandb_at_yahoo.com> wrote in message
news:a1d154f4.0402110051.773300b5_at_posting.google.com...

> "Ron" <support_at_dbainfopower.com> wrote in message news:<uJudnZ-bbbCtl7TdRVn-jA_at_comcast.com>...
> > 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
> > >
> > >

>
> Due to the basic nature of the OP's request:
> please try to learn people how to fish, do not fish on their behalf.
> You are disclosing way too much. OP should *learn* sql, he should not be
spoon fed.
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Thu Feb 19 2004 - 22:22:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US