By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,501 Members | 1,859 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,501 IT Pros & Developers. It's quick & easy.

extract numeric part from address

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
"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

P: n/a
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

P: n/a
"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

P: n/a
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.