extract numeric part from address

[David Chang]

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

[sybrandb@yahoo.com]

Re: extract numeric part from address

"David Chang" <chang_dj@yahoo .comwrote in message news:<8a1Wb.209 671$Rc4.1720145 @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

substr(<column> , 1, instr(...) -1 )

You fill in the ... as an exercise.


Sybrand Bakker
Senior Oracle DBA

[Ron]

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@yahoo .comwrote in message
news:8a1Wb.2096 71$Rc4.1720145@ 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
>
>

[sybrandb@yahoo.com]

Re: extract numeric part from address

"Ron" <support@dbainf opower.comwrote in message news:<uJudnZ-bbbCtl7TdRVn-jA@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@yahoo .comwrote in message
news:8a1Wb.2096 71$Rc4.1720145@ 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

[UNIXNewBie]

Re: extract numeric part from address

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@yahoo .comwrote in message
news:a1d154f4.0 402110051.77330 0b5@posting.goo gle.com...

"Ron" <support@dbainf opower.comwrote in message

news:<uJudnZ-bbbCtl7TdRVn-jA@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@yahoo .comwrote in message
news:8a1Wb.2096 71$Rc4.1720145@ 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