Re: Char to integer in "WHERE" clause
krishnakn@yahoo.com wrote:
>
>
>I need to convert the a character column to integer and perform
> some operations in a WHERE clause.
>
>For eg:
>[in foxpro]
>SELECT * FROM table1
>WHERE ABS( VAL( LEFT(zip_file,2)) - VAL(LEFT(zip_code,2))) > 2 ;>
>In the above example, I'm finding the difference between first
>two digits of 2 char columns.
>
>Pl. give me some suggestions...
>
>Thanks,
>
>krishnakumar
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Do you have access to using "stored procedures", if you do it
should be able to do
select * from table1 where st_proc_1(zip_file,zip_code) > 2
and
create procedure(zf char(..),zc char(..))
define zf2 integer;
define zc2 integer;
define retval integer;
let zf2=zf[1,2];
let zc2=zc[1,2];
let retval=zf2-zc2;
if retval<0 then # i think abs() does indeed exists, so
let retval=-retval; # let retval=abs(retval) might do
end if; # as well
return retval;
end procedure;
But you can do it directly too, if tried out eg.
select * from postnr wheredistrikt[1,3]="fr-"
and abs(distrikt[4,6]-850)<=20
from our zip-table and got
631152000 631152000 k90 850 fr-850 hvalba 95
631152000 631152000 k90 860 fr-860 sandvik 95
631152000 631152000 k90 870 fr-870 famjin 95
Where the 'distrikt' column is all char!
Informix has automatic type convertion by need, and a "VAL" operator
is not needed.
NOTE! You must make sure that all substrings are convertable or
there will be a conversion error. In my example I know that all those
with [1,3]="fr-" are valid for convertion - so they are extracted
first.
Finn E. Theodorsen///theodor@inet.uni2.dk///AtCbM///Legend#219605
TEN: Durax. Homepage: http://www.theodor.suite.dk/index.htm
All advertisments sent to the above address will be
treated as requests for computer support, and charged
accordingly. Sending these kind of messages equals an
acceptance of these terms. The minimum fee is $500.