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