Thanks, I think I go client side filtering route :)

On Tue, Oct 27, 2009 at 8:02 PM, Milan Babuskov <milanb@...> wrote:

>
>
> 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)
>
> BEGIN
> x = cast(str as integer);
> WHEN ANY ... or catch a particular conversion error, I don't know the
> code
> SUSPEND;
> END
>
> 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:
> http://www.guacosoft.com/xmlwizard
> ==================================
>
>
>


[Non-text portions of this message have been removed]