Home » SQL & PL/SQL » SQL & PL/SQL » Testing for Numerics??? (Oracle 10g)
Testing for Numerics??? [message #289934] Wed, 26 December 2007 14:54 Go to next message
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 #289935 is a reply to message #289934] Wed, 26 December 2007 15:01 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If you only need to see whether it is a number or not, apply the TO_NUMBER function to the column value. It will fail if there's also a city name in there (so you'll have to handle the exception).

As you didn't mention database version, I can only suggest you to pay attention to regular expressions; if available, they might help in separating postal numbers from city names.
Re: Testing for Numerics??? [message #289936 is a reply to message #289935] Wed, 26 December 2007 15:08 Go to previous messageGo to next message
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 #289937 is a reply to message #289934] Wed, 26 December 2007 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use an external table and then you have the full power of SQL to separate postal code from name.

Regards
Michel
Re: Testing for Numerics??? [message #289940 is a reply to message #289936] Wed, 26 December 2007 15:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
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, using
SELECT 
  REGEXP_SUBSTR(city_postal_number, '[[:alpha:]]+') city_name,
  REGEXP_SUBSTR(city_postal_number, '[[:digit:]]+') postal_code
FROM MY_TABLE;


icon10.gif  Re: Testing for Numerics??? [message #289942 is a reply to message #289940] Wed, 26 December 2007 16:11 Go to previous message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member
Little Foot,

THANK YOU VERY MUCH!!!

That is what I needed.

And Good examples, too.

Thanks again,
Lou

Smile
Previous Topic: Natural order by
Next Topic: Multiple Function definitions and Null datatype
Goto Forum:
  


Current Time: Fri Dec 09 15:15:52 CST 2016

Total time taken to generate the page: 0.24514 seconds