none
query help

    Question

  • converting data type varchar to numeric.

    I’m using the following:

    SELECT [TRAN_CODE]+', '+[INT_T]+', '+[DESC]+', '+[T_C]+', '+[NORM_TC]

       

    settings

    TRAN_CODE – varchar(20)

    INT_T – numeric(3,0)

    DESC – varchar(60)

    T_C – varchar(2)

    NORM_TC – varchar(20)

    Can anyone help me figure what is wrong.  I think I need to do some type cast or convert .

    Thanks,


    x

    Wednesday, July 02, 2014 6:26 PM

Answers

  • Hi, Following should give you correct result. SELECT [TRAN_CODE]+', '+Cast ([INT_T] as varchar(255))+', '+[DESC]+', '+[T_C]+', '+[NORM_TC] Regards If this what you looking for ,please mark this as answer.
    • Marked as answer by malibu2 Tuesday, July 08, 2014 2:56 PM
    Wednesday, July 02, 2014 6:36 PM
  • Add:

     COLLATE DATABASE_DEFAULT

    After each of your column names one by one till you find the culprit.

    For the record, mixing coalitions is a bad plan.

    • Marked as answer by malibu2 Tuesday, July 08, 2014 2:56 PM
    Wednesday, July 02, 2014 9:18 PM

All replies

  • Hi, Following should give you correct result. SELECT [TRAN_CODE]+', '+Cast ([INT_T] as varchar(255))+', '+[DESC]+', '+[T_C]+', '+[NORM_TC] Regards If this what you looking for ,please mark this as answer.
    • Marked as answer by malibu2 Tuesday, July 08, 2014 2:56 PM
    Wednesday, July 02, 2014 6:36 PM
  • What exactly do you want to convert; the mostly varchar type + one numeric to a varchar, or to a numeric (which type)? I guess the first, because in your SQL you add several commas as string value => ', '

    To varchar: Just convert the one numeric to varchar, then you can concatenate the varchar values

    SELECT [TRAN_CODE] + ', ' + CONVERT(varchar(10), [INT_T]) + ', ' + [DESC] + ', ' +[T_C] + ', ' + [NORM_TC]


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 02, 2014 6:37 PM
  • I'm getting the following error message now:

    Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in add operator.



    x

    Wednesday, July 02, 2014 8:12 PM
  • Add:

     COLLATE DATABASE_DEFAULT

    After each of your column names one by one till you find the culprit.

    For the record, mixing coalitions is a bad plan.

    • Marked as answer by malibu2 Tuesday, July 08, 2014 2:56 PM
    Wednesday, July 02, 2014 9:18 PM
  • Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "SQL_Latin1_General_CP1_CS_AS" in add operator.

    Seems your columns have different collation; the you have to cast them into one common collation, like:

    SELECT [TRAN_CODE] COLLATE SQL_Latin1_General_CP1_CS_AS
           + ', ' 
           + CONVERT(varchar(10), [INT_T])  COLLATE SQL_Latin1_General_CP1_CS_AS
           + ', ' 
           + [DESC]  COLLATE SQL_Latin1_General_CP1_CS_AS
           + ', ' 
           + [T_C]  COLLATE SQL_Latin1_General_CP1_CS_AS
           + ', ' 
           + [NORM_TC] COLLATE SQL_Latin1_General_CP1_CS_AS


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, July 03, 2014 7:05 AM