471,108 Members | 1,334 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

extract numeric part from address

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
Jul 19 '05 #1
4 4423
"David Chang" <ch******@yahoo.com> wrote in message news:<8a1Wb.209671$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
Jul 19 '05 #2
Ron

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_ad...isclaimer.html

"David Chang" <ch******@yahoo.com> wrote in message
news:8a1Wb.209671$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

Jul 19 '05 #3
"Ron" <su*****@dbainfopower.com> wrote in message news:<uJ********************@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_ad...isclaimer.html

"David Chang" <ch******@yahoo.com> wrote in message
news:8a1Wb.209671$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
Jul 19 '05 #4
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.



<sy******@yahoo.com> wrote in message
news:a1**************************@posting.google.c om...
"Ron" <su*****@dbainfopower.com> wrote in message news:<uJ********************@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_ad...isclaimer.html

"David Chang" <ch******@yahoo.com> wrote in message
news:8a1Wb.209671$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

Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Bernard Drolet | last post: by
3 posts views Thread by Joe | last post: by
44 posts views Thread by RB | last post: by
3 posts views Thread by zek2005 | last post: by
8 posts views Thread by Fabian Braennstroem | last post: by
3 posts views Thread by gmazza via AccessMonster.com | last post: by
4 posts views Thread by David Chang | last post: by
18 posts views Thread by Ecka | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.