Hi is there any function available in informix to convert char to num? 1

[CALYAN]

Is there any function available in Informix for converting
numeric to char and char to numeric?


regards
s.kalyan

 

[vpshriyan]

Hi,

If you are thinking of fancy built-in functions like val(), str() etc., you would feel disappointed, they are not there! Does that mean, you can not do such operation? No.

A workaround for converting decimal to char is to use the lpad() or rpad() built-in function and to conver char to decimal is to invoke the mathematical expression like:

select char_column * 1 from table_name ...
select char_column + 0 from table_name ...

select lpad(dec_column,10,' ') from table_name ...
select rpad(dec_column,10,' ') from table_name ...

Regards,
Shriyan

 

[sdlangers]

hi,

is there a workaround to test if a value in a column is numeric or not?

i.e. in sql server you can do:

select * from myTable where isnumeric(myColumn) = 1

 

[vpshriyan]

Hi sdlangers,

Well, you may use combinations trim(), lpad() & MATCHES keyword to simulate isnumeric() functioning.

Here is an example:

create temp table x (f1 char(5)) with no log;

insert into x values ('AAA');
insert into x values ('123');
insert into x values ('5');
insert into x values ('100');
insert into x values ('XXXX');

select * from x where trim(lpad(f1,20,' ')) matches '*[0-9]*';

Regards,
Shriyan