Is there any function in informix sql that converts char to int for use with relational operators (>, <, <= etc.)
I am using a char type column that contains numeric data, and need to compare data in this column with specific numbers. The query does not return correct results when I use the following where clause:
...where card_no[1,9] < "40110018"; In this case the query returns rows that contain 40110019 and greater numbers, even 9 and 10 digit numbers.

Where as if I use ...where card_no[1,9] < 40110018; --wihtout quotes
the query returns a character to numeric conversion error.

What is the correct way (maybe some function...) to compare char (actually integer) values with another number.

Thanks in advance.

Hi,

If you work with characters, the correct form is columnname[x,y], but if you work with numbers, then does not.
I think that you can create a stored procedure and convert value with a LET.

Gustavo.

You need to CAST the character field (or a piece of it) as a true number (like integer) and then you can perform your compares.

For example:

select first 20 ssn
from client_ssn
where cast (ssn as integer) between 300000000 and 400000000
;

select first 20 ssn
from client_ssn
where cast (ssn[4,5] as integer) = 67
;