Convert Char to numeric
Stephan Kirmse wanted to convert a CHAR column to numeric so AVG would work. Art Kagel advised giving version and platform, then suggested SELECT AVG(charcol::int) on 9.21+, 10.00 and 11.10+, and a stored procedure on older releases. Superboer and Jim Goldrick posted SPL functions with an exception handler that returns a default value on conversion failure (error -1213). No confirmation from the poster.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi everyone, I´m searching for a possibility to convert a char-field to a numeric one in a query. I want to use the avg-function, but that only allows numeric datatypes. Thanks for your help. Stephan
try:
CREATE function calc_number ( invalue_conv char(12) , retvalueiffail
int )
RETURNING int;
DEFINE retvalue int;
ON EXCEPTION IN (-1213)
RETURN retvalueiffail;
END EXCEPTION WITH RESUME ;
LET retvalue = invalue_conv;
RETURN retvalue;
END FUNCTION
Select calc_number (yourcolumntobeconvertedshouldbelessthen12,
-1) , ...
from yourtable
where calc_number (yourcolumntobeconvertedshouldbelessthen12, -1) !=
-1
-- assume that -1 is not part of your resultset......
Superboer.
way fast=http://www.clipjes.nl/clip/nederlands/n/normaal_-
_oerend_hard.html
On 23 apr, 08:15, Stephan Kirmse <kir...@fh-brandenburg.de> wrote:
> Hi everyone,
>
> I´m searching for a possibility to convert a char-field to a numeric one in a query.
> I want to use the avg-function, but that only allows numeric datatypes.
>
> Thanks for your help.
>
> Stephan
Stephan Kirmse wrote: > Hi everyone, > > I´m searching for a possibility to convert a char-field to a numeric one in a query. > I want to use the avg-function, but that only allows numeric datatypes. > It is REALLY helpful if you post your version and platform information since the capabilities of the various Informix versions can be vastly different. IFF you have 9.21 or later, 10.00, or 11.10 or later you can: SELECT AVG( charcol::int ) FROM mytable; If you have a earlier 9.xx release you may need to use a conversion function I think (don't remember whether the early 9's supported type casts or not). If you are running 5.xx, 6.xx, or 7.xx or SE you'll need to write a stored procedure to take in the char column and return a numeric type. Of course all of this will only work if the column only contains numeric characters. I'll not expand on what I think of the poor schema design that places a numeric calculation value in a character column. ;-( Art S. Kagel Oninit > Thanks for your help. > > Stephan > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >
here's one way
drop procedure audctoi;
create procedure audctoi (
str char(32))
returning
integer; define val integer;
on exception
let val = 0;
return val;
end exception
let val = str;
return val;
end procedure
select avg(audctoi("43")) from table;
Thank you,
Jim Goldrick
Judson University
1151 North State Street
Elgin, Illinois 60123
573-332-7739
http://www.judsonu.edu
jgoldrick@judsonu.edu
-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of Stephan Kirmse
Sent: Wednesday, April 23, 2008 1:16 AM
To: informix-list@iiug.org
Subject: Convert Char to numeric
Hi everyone,
I´m searching for a possibility to convert a char-field to a numeric one in a query.
I want to use the avg-function, but that only allows numeric datatypes.
Thanks for your help.
Stephan
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list