?
Solved

Converting String to numeric - want to detect before conversion.

Posted on 2007-08-06
15
Medium Priority
?
987 Views
Last Modified: 2008-01-09
Hi,

is there a way to check if a value that is returned is numeric?

I have some data which looks like below:

ColumnA   ColumnB
-----------  -----------
A              2.5
B              23.88
C              NA
D              9.5

I need to convert all the values in ColumnB into numeric, and sort the value such that B in columnA gets pickup in the end. But if I use "to_numeric" now to convert the field, I have problem coz' there's a "NA" in the value. How do I make sure that I can, perhaps by using "Case", pick up that it is non-numeric and skip converting it? Or if found "NA", convert into "-9999" ?
0
Comment
Question by:amai
  • 9
  • 4
  • 2
15 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 19637432
create or replace function safe_number( x in text ) returns double as $$
begin
  return x::double;
  exception when others then
  return 'Nan'::double;
end;
$$ language plpgsql;

select safe_number( 'NA' );
select safe_number( '9.5' );
0
 
LVL 22

Expert Comment

by:earth man2
ID: 19637433
create or replace function safe_number( x in text ) returns double as $$
begin
  return x::double;
  exception when others then
  return 'Nan'::double;
end;
$$ language plpgsql;

select safe_number( 'NA' );
select safe_number( '9.5' );
0
 

Author Comment

by:amai
ID: 19639299
can I do it without creating function? I don't have the privilege to create function, store procedure in this database.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 22

Accepted Solution

by:
earth man2 earned 500 total points
ID: 19641477
Now I am in front of my linux machine I can try it out...
create or replace function safe_number( x in text ) returns double precision as $$
begin
   return cast( x as double precision );
   exception when others then
   return cast( 'Nan' as double precision );
end;
$$ language plpgsql;
CREATE FUNCTION
treacle=> select safe_number( 'NA' );
 safe_number
-------------
         NaN
(1 row)

treacle=> select safe_number( 9.1 );
 safe_number
-------------
         9.1
(1 row)
0
 
LVL 22

Expert Comment

by:earth man2
ID: 19641553
Cannot create function eh ?
Try.

select case when columnB='NA' then cast('NaN' as double precision ) else cast( ColumnB as double precision end from YOUR_TABLE;
0
 
LVL 22

Expert Comment

by:earth man2
ID: 19641572
select case when columnB='NA' then cast('NaN' as double precision ) else cast( ColumnB as double precision ) end from YOUR_TABLE;
0
 
LVL 22

Expert Comment

by:earth man2
ID: 19643690
treacle=> create table xy( ColumnB text);
CREATE TABLE
treacle=> insert into xy values ('9.2');
INSERT 0 1
treacle=> insert into xy values ('NA');
INSERT 0 1
treacle=> select case when columnB='NA' then cast('NaN' as double precision ) else cast( ColumnB as double precision ) end from xy;
 columnb
---------
     9.2
     NaN
(2 rows)
0
 
LVL 19

Expert Comment

by:grant300
ID: 19647790
I believe you still have a problem here since a column can only contain one datatype.  I strongly suspect that, in earthman2's last example, the value 9.2 is being implicitly converted back to a string.

This is what NULLs are for.  So to rework the select statement...

select cast((case when columnB='NA' then NULL else ColumnB end) as double precision) from xy

Notice that the entire column expression is cast to double precision as the last step.  Since NULL can successfully be cast into double precision, this will work and actually return a numeric representation of the number instead of another string.

Regards,
Bill
0
 
LVL 22

Expert Comment

by:earth man2
ID: 19651969
I strongly suspect that, in earthman2's last example, the value 9.2 is being implicitly converted back to a string.

No it isn't - Not a Number (Nan) is an IEEE Floating Point entity.  Multiplying a float by Nan yields Nan,  Yes you could use NULL instead.
0
 

Author Comment

by:amai
ID: 19658851
Hi,

i've tried, thanks, but instead of 23.88 (in my example), "NaN" got pick up as the Max number instead.... is there a way that this doesn't happen?
0
 
LVL 19

Expert Comment

by:grant300
ID: 19663474
Try using my version with the NULL instead.  The database aggregate functions know how to handle NULLs.

Regards,
Bill
0
 
LVL 22

Expert Comment

by:earth man2
ID: 19675697
treacle=> select max( case when columnB='NA' then cast('-infinity' as double precision ) else cast( ColumnB as double precision ) end ) from xy;
 max
-----
 9.2
(1 row)
0
 
LVL 19

Expert Comment

by:grant300
ID: 19679725
Earthman2,

I am curious; why the aversion to NULLs?  They are native to the database and all of SQL and the database specific extensions understand it and deal with it in a consistant fashion.

Just wondering....

Bill
0
 
LVL 22

Expert Comment

by:earth man2
ID: 19680005
it is simpler to deal with one value than to have to check for null condition at the different steps of a complex arithmetic sequence.  That's what the IEE standard is about.

I don't have a specific aversion to nulls per se, they can just make programming logic more difficult.

eg

rather than putting an interval end timestamp to NULL as default it makes a lot of sense to put it to infinity::timestamp instead when the period is still current.
0
 
LVL 19

Expert Comment

by:grant300
ID: 19680122
I agree with you to a point.  For instance if you are effective dating records as you allude to, the Infinite date is a great idea.  I typically use Dec 31 9999 in Sybase and Oracle.

I think this case is a bit different.  They really have a NULL condition ("no data here") but chose to put a man-readable string in it's place.

Of course, this whole thread really becomes a case study in why numbers should be stored in numeric fields, not strings 8-)

Regards,
Bill
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question