Testing for Numerics??? [message #289934] |
Wed, 26 December 2007 14:54  |
pcd_lou
Messages: 63 Registered: October 2007 Location: Florida
|
Member |
|
|
Hi all,
I have a need to find a numeric value within a field. Specificly, I'm getting a field from a flat file (CSV) where it may contain a Postal Code (Numeric) and the City Name, yes, both in the same field. I need to parce out the Postal Code from the City Name. Both are variable lengths within the field. But sometimes it is just the Postal Code. If I could tell if it was just the Postal Code, then I would know that the next field was the City Name. These addresses are European, so sometimes the Postal Code is before the City and sometimes after the city.
Is there a way to test to see if an element is all numeric?
This would help a lot.
Thanks,
Lou
|
|
|
|
Re: Testing for Numerics??? [message #289936 is a reply to message #289935] |
Wed, 26 December 2007 15:08   |
pcd_lou
Messages: 63 Registered: October 2007 Location: Florida
|
Member |
|
|
LittleFoot,
Thank you, but the field is coming from an Excel spreadsheet. I don't have any control on what is in it.
I thought the posting shows that I am on Oracle 10g using PL/SQL.
The To_Number function is fine, but How do I "Handle the Exception" without ending the program? Because I would want to take the first 5 characters and see if it is numeric and parce it apart from there.
I don't know how to do this. The only Exception I am monitoring is End-Of-File on the Get of the flat file. At that point, I am done and end the program.
Let me know.....
Thanks,
Lou
|
|
|
|
Re: Testing for Numerics??? [message #289940 is a reply to message #289936] |
Wed, 26 December 2007 15:42   |
 |
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Oops! Sorry, I didn't pay attention to version information. Well, 10g supports regular expressions (if it means anything to you).
I understood that data already IS in an Oracle table (loaded via SQL*Loader or used as an external table, as Michel suggested). Something like this:CREATE TABLE my_table
(city_postal_number varchar2(50),
city_name varchar2(45),
postal_code number(5)
); Currently, you have this:
city_postal_number city_name postal_code
------------------ --------- -----------
10000 Zagreb
Koprivnica 48000
Paris
21000
Dubrovnik
... and would like to do the UPDATE and fill information into the 'city_name' and 'postal_code' columns. For example, usingSELECT
REGEXP_SUBSTR(city_postal_number, '[[:alpha:]]+') city_name,
REGEXP_SUBSTR(city_postal_number, '[[:digit:]]+') postal_code
FROM MY_TABLE;
|
|
|
|