none
Conversion failed when converting the varchar value '00000001_' to data type int.

    Question

  • select convert(int,RTRIM('00000001    '))

    select convert(int,RTRIM('00000001    '))

    I've been getting error converting varchar value to data type int

    anyone knows how to solve this one?

    Tuesday, July 12, 2011 2:14 AM

Answers

  • I got the same error.

    Here is a workaround - search for first non-digit error and strip everything starting with non digit

    declare @test varchar(20)
    set @test = '000001' + char(9)
    select CAST(substring(@test,1,patindex('%[^0-9]', @test + ' ') -1) as int)
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by nuttynuts Tuesday, July 12, 2011 3:21 AM
    Tuesday, July 12, 2011 2:35 AM

All replies

  • How can we reproduce your problem / error?

     

     


    AMB

    Some guidelines for posting questions...

    Tuesday, July 12, 2011 2:23 AM
  • Hello Hunchback thanks for the quick reply

    here my sql statement SELECT CAST('00000001    ' AS INT)

    Please note that the spaces in between 1 to '  is not really a space but generated by pressing tab . I mean I press tab in between 1 to '.


    nuttynuts
    Tuesday, July 12, 2011 2:29 AM
  • I got the same error.

    Here is a workaround - search for first non-digit error and strip everything starting with non digit

    declare @test varchar(20)
    set @test = '000001' + char(9)
    select CAST(substring(@test,1,patindex('%[^0-9]', @test + ' ') -1) as int)
    



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    • Marked as answer by nuttynuts Tuesday, July 12, 2011 3:21 AM
    Tuesday, July 12, 2011 2:35 AM
  • Hello Naomi,

    Your workaround works like a charm.

    Regards


    nuttynuts
    Tuesday, July 12, 2011 3:12 AM