Subject Re: [firebird-support] Select when string can be cast?
Author Milan Babuskov
Harriv wrote:
> Is there a way to select by a VarChar field all the records which can
> be casted as integer?
> Something like: Select * from MYTABLE where Cast(ColumnA as Integer)

Yes. Not trivial though. You should combine EXECUTE BLOCK (because
procedural programming is needed) with EXECUTE STATEMENT (because you
will find field names dynamically).

Select all the table fields with select from RDB$RELATION_FIELDS. After
that, generate statement with that field and feed it to EXECUTE
STATEMENT. You SELECT statement should cast everything to VARCHAR(big)
to aviod getting exception if some values can be converted to integer
and others can't. Maximum size of varchar depends on charset you use, so
"big" can be 8000 for example. Well, you won't be able to convert
integers bigger than 64bit integer, but you need to catch that case
after you get the data in.

While going throught results of select, try to CAST them to integer, but
wrap a CAST in BEGIN..WHEN..END block, to catch exceptions

declare variable x integer;
declare variable str varchar(2000)

x = cast(str as integer);
WHEN ANY ... or catch a particular conversion error, I don't know the

Anyway, while it is doable in PSQL it's cumbersome, complicated and
inefficient. It is much better to attack the problem on the client side.
Fetch the data to your program and try conversions there.

Milan Babuskov

The easiest way to import XML, CSV
and textual files into Firebird: