Post reply

String conversion problem

  • July 4, 2011 at 8:35 am

    #389890

    Hi,

    I am having the following problem. I am loading a string formated number from one table so as to use it in a stored procedure.

    the select query goes like that e.g.

    Select CASE WHEN LEN(code) = 12 THEN LEFT(code, 12)

    WHEN LEN(code) = 15 THEN SUBSTRING(code, 4, 12)

    ELSE LEFT(code, 12)

    END CodeNumber

    from Customer

    The field "code" is a varchar (20).

    The result is then being used in the stored procedure and converted into a bigint. However, although the result is always a twelve digit number e.g. "086597255403" two rows return a conversion error: "Error converting data type varchar to bigint."

    I confirmed the error by running an ISNUMERIC on the two rows and the result was indeed zero. Can a number be interpreted someway as a letter? Could it be an encoding issue?

  • July 4, 2011 at 8:51 am

    #1347903

    what are the two values causing errors?

  • July 4, 2011 at 8:58 am

    #1347913

    They are two 12 digit numbers as the rest of the results.

    I just did a select on the original table for these specific rows and the return result is that they have a length of 13 and ISNUMERIC = 0. However when I chose them through left(code, 12) the return result is only the number so how can it still see it as a character?

  • July 4, 2011 at 8:59 am

    #1347915

    ISNUMERIC is not always the best way to check that a column contains (what you may think is) a purely numeric value.

    See http://www.sqlservercentral.com/articles/IsNumeric/71512/

    BrainDonor.

    Steve Hall
    Linkedin
    Blog Site

  • July 5, 2011 at 12:59 am

    #1348078

    Thanks brain for the article! It was really interesting. I managed to solve my problem eventually. A space was carried along with the number by a misstyped "case" of mine.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply