none
TSQL Error Converting string to int

    Question

  • Hi

    When Running the below Query it gives me erroe "Conversion failed when converting the varchar value 'E55B1BAA' to data type int." . I have used the Isnumeric check, still it fails. Is there any alternate solution to the query. Please note the column contains both Numeric and alphanumeric.
    declare @userid int,
    @testid int,
    @versionid int


    select @userid=53322,@testid=1391,@versionid=2


    select q.question_text, sq.question_id ,question_order,IsNumeric(question_order)
    from stu_test_questions sq With (Nolock)
    inner join question q  With (Nolock)on sq.question_id = q.question_id
    INNER JOIN section_questions sqt ON q.question_id = sqt.question_id
    where sq.version_id = @versionid and sq.test_id = @testid and sq.student_id = @userid and sqt.inactive_flag = 0 
    order by Case When IsNumeric(question_order) = 0 Then Cast(question_order   as Varchar(20)) Else Cast(question_order as INT)   End
    Monday, January 21, 2013 6:47 AM

Answers

  • Hi ,

    Thanks Everyone, i got the solution anyways. Apparently i can order by both so i had to break the order by clause. Did the trick for me.

    declare @userid int,
    @testid int,
    @versionid int


    select @userid=53322,@testid=1391,@versionid=2


    select q.question_text, sq.question_id ,question_order,IsNumeric(question_order)
    from stu_test_questions sq With (Nolock)
    inner join question q  With (Nolock)on sq.question_id = q.question_id
    INNER JOIN section_questions sqt ON q.question_id = sqt.question_id
    where sq.version_id = @versionid and sq.test_id = @testid and sq.student_id = @userid and sqt.inactive_flag = 0 
    order by Case When IsNumeric(question_order) = 1 Then Cast(question_order as INT)   End ,  question_order   



    Regards, PS

    • Marked as answer by Pritam_Shetty Monday, January 21, 2013 7:15 AM
    Monday, January 21, 2013 7:15 AM

All replies

  • It does not look any reason for the Cast to int. See the below example. You cant have integer conversion along with character in ORDER by in the same statement..

    Drop table t1 Create Table dbo.t1(Col1 varchar(50)) Insert into t1 Select '1234E' Insert into t1 Select '1234' Insert into t1 Select '1234D' Select * From t1 --Try the below order by Case When IsNumeric(col1) = 0 Then Cast(col1 as Varchar(20)) Else Cast(col1 as INT) End --Try the below --order by Col1 desc



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, January 21, 2013 7:01 AM
  • order by Case When IsNumeric(question_order) = 0 Then Cast(question_order   as Varchar(20)) Else Cast(question_order as INT)   End
    I think the problem is due to the ORDER BY Clause which has return type of higher precedence column so INT takes the higher precendence and SQL SERVER tries to convert string into INT , thus Error

    Thanks and regards, Rishabh K

    Monday, January 21, 2013 7:04 AM
  • Hi Latheesh,

    I tried the first approach doesnt work, it still gives me error. (order by Case When IsNumeric(col1) = 0 Then Cast(col1 as Varchar(20)) Else Cast(col1 as INT) End) in my case.

    Second one (order by Col1 desc) is not answer for me. I gives me problem in logic if there is values like 1,11,2 so its sort like 1,11,2 where as i want is 1,2,11.

    Thanks anyways. SQL server still takes int as precedence i guess.



    Regards, PS

    Monday, January 21, 2013 7:11 AM
  • Hi ,

    Thanks Everyone, i got the solution anyways. Apparently i can order by both so i had to break the order by clause. Did the trick for me.

    declare @userid int,
    @testid int,
    @versionid int


    select @userid=53322,@testid=1391,@versionid=2


    select q.question_text, sq.question_id ,question_order,IsNumeric(question_order)
    from stu_test_questions sq With (Nolock)
    inner join question q  With (Nolock)on sq.question_id = q.question_id
    INNER JOIN section_questions sqt ON q.question_id = sqt.question_id
    where sq.version_id = @versionid and sq.test_id = @testid and sq.student_id = @userid and sqt.inactive_flag = 0 
    order by Case When IsNumeric(question_order) = 1 Then Cast(question_order as INT)   End ,  question_order   



    Regards, PS

    • Marked as answer by Pritam_Shetty Monday, January 21, 2013 7:15 AM
    Monday, January 21, 2013 7:15 AM
  • Ohhh sorry for the confusion:

    I wanted you to try both to understand the first one is not possible. See my first thread:"You cant have integer conversion along with character in ORDER by in the same statement.." Though,Nice to see the issue has solved.

    However, I dont think your ORDER BY is still working....See the below:

    Drop table t1 Create Table dbo.t1(Col1 varchar(50)) Insert into t1 Select '1234E' Insert into t1 Select '1234' Insert into t1 Select '1234D' Insert into t1 Select 'D1234D' Insert into t1 Select '11234D' Select * From t1 order by Case When IsNumeric(col1) = 1 Then Cast(col1 as INT) End desc



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    Monday, January 21, 2013 7:19 AM
  • Hi Lathesh, 

    Thanks,

    Thats right this would work only for int column. But i wanted sorting on both cases. If int the sort by int Values. If varchar then sort by varchar values.


    Regards, PS

    Monday, January 21, 2013 7:25 AM
  • Yes, if you do a Case where one of the when conditions results in a varchar and the other results in an int, SQL will try to convert the varchar value to an int (because int has a higher precedence than varchar).  So you have to get the int converted to a varchar.  But since you want 2 to sort in before 11, you must be clever.  So do (I'm assuming your column is varchar(20) from the above code you posted, if it something else, just replace the 20 in the below code with the actual length of your column.

    -- If you are on SQL 2008R2 or earlier
    order by case when col1 like '%[0-9]%' Then col1 Else Right(Replicate('0', 20) + col1, 20)   End
    -- If you are on SQL 2012 or later
    order by case when Try_Convert(int, col1) Is Null Then col1 Else Right(Replicate('0', 20) + col1, 20)   End
    

    That will convert integer values to strings that are 20 characters long by adding leading zeros.  So 2 becomes <19 0's>2 and 11 becomes <18 0's>11 and 2 will sort before 11.

    And you probably don't want to use IsNumeric because it does unexpected things (like it will call the character string '14.7E5' numeric).

    As you now know mixing integers and strings into the same column causes you problems like this and where possible should be avoided in the future when designing databases.

    Tom


    Monday, January 21, 2013 7:43 AM
  • Thanks Tom,

    The Query is Good but wont help in my case. The number range can go to any limit. I would rather say bad DB design, should have sorted out that first place. Just a bit to late now to do any changes.



    Regards, PS

    Monday, January 21, 2013 11:26 AM
  • You may try the below:(Not tested thoroughly)

    Drop table #test create table #test(val varchar(10)) insert into #test select '1002' union all select '29C' union all select '1' union all select '205' union all select '105A' union all Select '12344E' union all Select '1234' union all Select '1234D' union all Select 'D1234D' union all Select '11234D' select * from #test Order by substring(val+'a',1,patindex('%[a-zA-Z]%',val+'a')-1)*1 asc



    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, January 21, 2013 12:58 PM