How to make a safe varchar() to int conversion

I'm selecting a big group of records for output, and I need to convert a
couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
myintfield from ...)
Problem is, some erroneous data has non-numeric characters in it, and SQL
Server kills the whole SELECT, outputting no rows (!!!).
Is there any way to get SQL to just put NULL or 0 in for erroneous data -
the way you can use SET ARITHABORT to have it ignore numeric errors and keep
processing?
Thanks!
- NevynHello Nevyn,
One of the best way's I've found to do this is to use a LIKE clause in your
select statement
SELECT * FROM myTable WHERE numCol NOT LIKE '%[a-z]%'
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

> I'm selecting a big group of records for output, and I need to convert
> a couple columns from varchar to int. (SELECT CAST(mycharfield as int)
> as myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and
> SQL Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous
> data - the way you can use SET ARITHABORT to have it ignore numeric
> errors and keep processing?
> Thanks!
> - Nevyn
>|||You can just add
"where isnumeric(col)=1"
or
"where col like '%[^0-9]%'"
to your query
-oj
"Nevyn Twyll" <astian@.hotmail.com> wrote in message
news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...
> I'm selecting a big group of records for output, and I need to convert a
> couple columns from varchar to int. (SELECT CAST(mycharfield as int) as
> myintfield from ...)
> Problem is, some erroneous data has non-numeric characters in it, and SQL
> Server kills the whole SELECT, outputting no rows (!!!).
> Is there any way to get SQL to just put NULL or 0 in for erroneous data -
> the way you can use SET ARITHABORT to have it ignore numeric errors and
> keep processing?
> Thanks!
> - Nevyn
>|||That would be "where col NOT like '%[^0-9]%'"
Gert-Jan
oj wrote:
> You can just add
> "where isnumeric(col)=1"
> or
> "where col like '%[^0-9]%'"
> to your query
> --
> -oj
> "Nevyn Twyll" <astian@.hotmail.com> wrote in message
> news:eYNoatNCFHA.268@.TK2MSFTNGP10.phx.gbl...|||Tks for the correction.
-oj
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42015A90.8863CB3B@.toomuchspamalready.nl...
> That would be "where col NOT like '%[^0-9]%'"
> Gert-Jan
>
> oj wrote: