8114 Workaround

The very simplified version of my problem is that these

Select DISTINCT Cast(KWID as NUMERIC)
FROM OV_MID

Select DISTINCT Convert(Numeric,KWID)
FROM OV_MID

should work, but don't because KWID is a varchar and somewhere in there is something that won't convert.

I get this error:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

I would love to find out which rows are causing the error, but more importantly I would like to have a Null value where the conversion doesn't work and the numeric values where it does work.

I have already deleted all obvious non-numeric characters, but I believe there are some line terminators being read as carriage returns in this table. :confused:

Any workaround or way to determine which rows have KWID that cannot be converted to numeric would be most appreciated.

Thanks!Just in case anyone actully reads this and has the same question. I solved it with:

SELECT
Case
When ISNUMERIC(KWID) = 1
THEN Cast(KWID as NUMERIC)
ELSE Cast(null as NUMERIC)
END
FROM OV_MID

maybe that will help someone else out who is going as crazy as I was. :o

36 digit number

Hi I have this column numbers as varchars.
Length is 36
I need them to be numeric so I can run calcs on them
I can't find a datatype that allows that.
what the heck?
thanksTry with decimal.
Example:
declare @.d decimal (36, 0)
set @.d = 999999999999999999999999999999999999
select @.d
go
AMB
"Candor Feg" wrote:

> Hi I have this column numbers as varchars.
> Length is 36
> I need them to be numeric so I can run calcs on them
> I can't find a datatype that allows that.
> what the heck?
> thanks
>
>|||Lookup NUMERIC and DECIMAL data types in SQL Server Books Online.
Anith|||Just out of curiosity, what is being represented as a 36 decimal number?
"Candor Feg" <ddsd*f.com> wrote in message
news:i9KdnbbWotCeOAnfRVn-tA@.comcast.com...
> Hi I have this column numbers as varchars.
> Length is 36
> I need them to be numeric so I can run calcs on them
> I can't find a datatype that allows that.
> what the heck?
> thanks
>|||The chances of me being the antichrist.
"JT" <someone@.microsoft.com> wrote in message
news:%23lFGkfVYFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Just out of curiosity, what is being represented as a 36 decimal number?
> "Candor Feg" <ddsd*f.com> wrote in message
> news:i9KdnbbWotCeOAnfRVn-tA@.comcast.com...
>