none
Conversion failed when converting the varchar value to data type int

    Question

  • Hi

    I have a query with a case statment like the following:

    SELECT DISTINCT CASE WHEN Priority IS NULL THEN
    cast('Priority Not Specified' as VARCHAR(50)) ELSE cast(Priority as INT) END AS Priority_New
    FROM   Mydatabase

    but I am getting the following error:

    Conversion failed when converting the varchar value 'Priority Not Specified' to data type int.

    I would like the case to display the following:

    Priority Not Specified
    1
    2
    3
    4
    5

    Can anyone advise?

    thanks

    Thursday, December 06, 2012 12:31 PM

Answers

  • You cant have two different datatype casting for single column.

    Try the below:

    SELECT DISTINCT CASE WHEN Priority IS NULL THEN cast('Priority Not Specified' as VARCHAR(50)) ELSE cast(Priority as varchar(50)) END AS Priority_New FROM Mydatabase



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

    • Marked as answer by dev-13 Thursday, December 06, 2012 1:02 PM
    Thursday, December 06, 2012 12:37 PM

All replies

  • You cant have two different datatype casting for single column.

    Try the below:

    SELECT DISTINCT CASE WHEN Priority IS NULL THEN cast('Priority Not Specified' as VARCHAR(50)) ELSE cast(Priority as varchar(50)) END AS Priority_New FROM Mydatabase



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

    • Marked as answer by dev-13 Thursday, December 06, 2012 1:02 PM
    Thursday, December 06, 2012 12:37 PM
  • I'm not getting the use of

    cast('Priority Not Specified' as VARCHAR(50))

    as  the text 'Priority Not Specified' is already varchar.

    Try below and let me know if it works.

    SELECT DISTINCT CASE WHEN Priority IS NULL THEN
    'Priority Not Specified' ELSE cast(Priority as VARCHAR(50)) END AS Priority_New
    FROM   Mydatabase


    Best Regards, Venkat


    • Edited by Venkats Thursday, December 06, 2012 12:44 PM typo
    Thursday, December 06, 2012 12:42 PM
  • As you specified both VARCHAR(50)  and  INT for  CASE value type, DB engine must convert them to one type, because any expression including CASE... END  has exactly one type. Due to conversion rules it  assumes your CASE is of INT type and  tries convert varchar() to INT.

    Surely different types  cann't be mixed in one column. You should choose which type is appropiate under circumstances. If it's a user interface thing then  cast both to varchar(). If it's intemidiate result i'd better leaved NULL as it is.


    Serg


    • Edited by SergNL Thursday, December 06, 2012 12:46 PM
    Thursday, December 06, 2012 12:45 PM
  • Please avoid the using int and varchar for same column, try the below query.

    SELECT DISTINCT CASE WHEN Priority IS NULL THEN 
    cast('Priority Not Specified' as VARCHAR(100)) ELSE cast(Priority as varchar(100)) END AS Priority_New
    FROM   Mydatabase


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Thursday, December 06, 2012 12:47 PM
  • CREATE TABLE #t (id VARCHAR(20))

    INSERT INTO #t VALUES ('1'),('2'),('3A'),('4')

    SELECT CAST(id AS INT) FROM #t

    1
    2
    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value '3A' to data type int.

    ---sql server 2012
    SELECT TRY_CONVERT(INT, id)  FROM #T
    1
    2
    NULL
    4
    ---sql server 2005/2008/2008e2
    SELECT * FROM #t WHERE 1 =CASE WHEN PATINDEX('%[^0-9^-]%', id) = 0 
            AND CHARINDEX('-', id) <= 1 THEN  1 END 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, December 06, 2012 12:52 PM
  • Thank you, it works excellent.  Wasnt sure about the different datatypes
    Thursday, December 06, 2012 1:03 PM