> 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
>

"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
>

"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:
>>
>> 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
>> >

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...
> > 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