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

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