Is there a SQL equivalent to the Informix MAXINT?
I have a record with a varchar(27) field and I only want to copy records from it where the integer value can fit in a integer field. I want to do something like the following. Thanks!
create table #t1
insert into #t1 values (123456789)
insert into #t1 values (12345678901234567890)
create table #t2
insert into #t2
select * from #t1
where varcharfield < MAXINT
PS. At the moment I'm using:
where cast(varcharfield as bigint) between -2147483648 and 2147483647 but it seems a bit hokey
Hokey perhaps, but it essentially amounts to similar (installed) functionality, which typically also performs some combination of the following additional kinds of tasks:
1 parse out non-numeric chars or reject and insert an identifier value, a null, etc. when non-numeric chars are encountered
2 replace certain non numeric chars o, O --> 0; !, l, i, I --> 1
3 reject or truncate any resulting digit strings that would necessarily overflow the Cast function