cast not valid

Hello:
I want to make a query that return all record that don't have one valid
cast, something like this:
select * from table where cast(field as bigint) is valid
how can I do that or some variant?. the real problem is to import some table
in a dts but when I try to convert the str to bigint, raise one execption,
that I want to jump and eliminate this record but continue, instead the dts
stop.
Best regards,
Owen.Owen wrote:
> Hello:
> I want to make a query that return all record that don't have one
> valid cast, something like this:
> select * from table where cast(field as bigint) is valid
> how can I do that or some variant?. the real problem is to import
> some table in a dts but when I try to convert the str to bigint,
> raise one execption, that I want to jump and eliminate this record
> but continue, instead the dts stop.
> Best regards,
> Owen.
CAST is not a BOOLEAN function. That is, it does not return whether a
value _can_ be converted from one type to another. It explicitly tries
to convert and throws an exception if a failure occurs. You could use
the ISNUMERIC() function or roll your own integer check function or use
the one here:
http://www.aspfaq.com/show.asp?id=2390
David Gugick
Quest Software
www.imceda.com
www.quest.com|||SELECT *
FROM Table
WHERE (x NOT LIKE '%[^0-9]%'
AND LEN(x) BETWEEN 1 AND 18)
OR x IS NULL
David Portas
SQL Server MVP
--