Converting char data values to numbers

[schintapalli]

In Informix, I have a column which stores only numbers but the data type for that column is defined as char. Is there any way that I can convert this char data into numeric so that I can use that in the where clause as well as in order by clause.
Eg: The column data has values '11' , '103','412', '92'. whene I use a select with order by I am getting the result set as '11' , '103','412', '92'. where as I would like get '11','92','103','412'. Also I would like to use this column in where clause to filter, so that I can get records only with value above 50, say 'where column_value > 50'. How can I achieve this, any function is available in informix for this?

Regards

 

[vpshriyan]

Hi,

You may take advantage of mathematic formula to achieve this, by adding 0 (zero) to the field value. Example below demonstrates this.

create temp table x (f1 char(3)) with no log;
insert into x values ('11');
insert into x values ('92');
insert into x values ('103');
insert into x values ('412');
select f1+0 from x order by 1;
select f1+0 from x where f1+0 > 50 order by 1;

However, since a char column is used to store numeric values, it may happen that alphas and other special characters may have been seeped in. To singleout such rows you may fire SQLs similar to the following:
select * from x where upper(f1) matches '*[A-Z]*';
at al.

Regards,
Shriyan
"Knowledge without common sense is folly."

 

[vpshriyan]

Alternatively you may consider the combination of in-built sql functions as below:

select lpad(trim(f1),10,' ') from x order by 1;

 

[PHV]

And why not do a ALTER TABLE ... MODIFY ?

Hope This Help
PH.