Results 1 to 2 of 2

Thread: 8114 Workaround

  1. #1
    Join Date
    Dec 2004
    Location
    Oakland, CA
    Posts
    2

    Question Unanswered: 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.

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

    Thanks!

  2. #2
    Join Date
    Dec 2004
    Location
    Oakland, CA
    Posts
    2

    answer

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •