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

"David Chang" <chang_dj (AT) yahoo (DOT) com> wrote

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

You fill in the ... as an exercise.


Sybrand Bakker
Senior Oracle DBA

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" <chang_dj (AT) yahoo (DOT) com> wrote

"Ron" <support (AT) dbainfopower (DOT) com> wrote

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

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 (DOT) com> wrote