Converting a COALESCE statement to varchar

  • meichmann

    SSCommitted

    Points: 1899

    Hello Everyone,
         I don't know if this is possible but I figured I would give it a try....

    I have a table with 6 columns:

    Label
    IntValue
    DecimalValue
    DateTimeValue
    StringValue
    MoneyValue

    The label column is a string, but the other columns datatype is the same as the column name (IntValue is an int, etc)

    Now,  each row will have a label, but only 1 value for the other 5.  in other words, it will only have an intValue, or a DatetimeValue, etc...  a row will not have 2 values and a label.

    So here's my question.  Can I use COALESCE and convert it to a string?  I tried using this:

    CAST(COALESCE(IntValue, DecimalValue, DateTimeValue, StringValue, MoneyValue) AS varchar(50)) AS CustomValue

    It processes the first 3 or 4 rows then bombs with this error:

    Conversion failed when converting date and/or time from character string.


    is my format right?  if so, is the COALESCE coming across bad data?

    Thanks for your help as always!!!

  • drew.allen

    SSC Guru

    Points: 76739

    meichmann - Monday, July 30, 2018 12:32 PM

    Hello Everyone,
         I don't know if this is possible but I figured I would give it a try....

    I have a table with 6 columns:

    Label
    IntValue
    DecimalValue
    DateTimeValue
    StringValue
    MoneyValue

    The label column is a string, but the other columns datatype is the same as the column name (IntValue is an int, etc)

    Now,  each row will have a label, but only 1 value for the other 5.  in other words, it will only have an intValue, or a DatetimeValue, etc...  a row will not have 2 values and a label.

    So here's my question.  Can I use COALESCE and convert it to a string?  I tried using this:

    CAST(COALESCE(IntValue, DecimalValue, DateTimeValue, StringValue, MoneyValue) AS varchar(50)) AS CustomValue

    It processes the first 3 or 4 rows then bombs with this error:

    Conversion failed when converting date and/or time from character string.


    is my format right?  if so, is the COALESCE coming across bad data?

    Thanks for your help as always!!!

    COALESCE() requires that all of it's parameters be of compatible data types and tries to convert everything to the highest precedence data type (date/time here).  The error says that you passed in a string that is not parseable as a date. To resolve this, you'll need to CAST each of the fields to VARCHAR(50) before using the COALESCE.  There is another option, but I don't know that it will preform any better.  You could try the following subquery instead, which converts it to XML and then to VARCHAR(50).


    (
    SELECT
        IntValue AS [text()],
        DecimalValue AS [text()],
        DateTimeValue AS [text()],
       StringValue AS [text()],
       MoneyValue AS [text()]
    FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(50)')

    It's important that you use those column aliases, or it will create XML tags for each of the fields.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Luis Cazares

    SSC Guru

    Points: 183638

    meichmann - Monday, July 30, 2018 12:32 PM

    So here's my question.  Can I use COALESCE and convert it to a string?  I tried using this:

    CAST(COALESCE(IntValue, DecimalValue, DateTimeValue, StringValue, MoneyValue) AS varchar(50)) AS CustomValue

    Basically, you would need to do the opposite. You need to convert everything into a string and then use. Basically, you would need to do the opposite. You need to convert everything into a string and then use COALESCE.

    COALESCE(CAST( IntValue AS varchar(50))
            ,CAST( DecimalValue AS varchar(50))
            ,CAST( DateTimeValue AS varchar(50))
            ,CAST( StringValue AS varchar(50))
            ,CAST( MoneyValue AS varchar(50)))

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • jcelko212 32090

    SSCrazy Eights

    Points: 9032

    meichmann - Monday, July 30, 2018 12:32 PM

    Hello Everyone,
         I don't know if this is possible but I figured I would give it a try....

    I have a table with 6 columns:

    Label
    IntValue
    DecimalValue
    DateTimeValue
    StringValue
    MoneyValue

    The label column is a string, but the other columns datatype is the same as the column name (IntValue is an int, etc)

    Now,  each row will have a label, but only 1 value for the other 5.  in other words, it will only have an intValue, or a DatetimeValue, etc...  a row will not have 2 values and a label.

    So here's my question.  Can I use COALESCE and convert it to a string?  I tried using this:

    CAST(COALESCE(IntValue, DecimalValue, DateTimeValue, StringValue, MoneyValue) AS varchar(50)) AS CustomValue

    It processes the first 3 or 4 rows then bombs with this error:

    Conversion failed when converting date and/or time from character string.


    is my format right?  if so, is the COALESCE coming across bad data?

    Thanks for your help as always!!!

    COALESCE() works by first looking down the parameter list, finding the highest data type in that list, and converting all of the other parameters to that type. In this case, you're going to have everything cast to a VARCHAR(n). In the second pass, the function then moves left to right until it finds the first non-null value in the converted parameter list..

    Can you tell us what you're trying to do? This is just a little too weird to be a real programming problem. My guess (why did you fail to post data?) are invalid and could not be cast.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • drew.allen

    SSC Guru

    Points: 76739

    jcelko212 32090 - Monday, July 30, 2018 1:59 PM

    meichmann - Monday, July 30, 2018 12:32 PM

    Hello Everyone,
         I don't know if this is possible but I figured I would give it a try....

    I have a table with 6 columns:

    Label
    IntValue
    DecimalValue
    DateTimeValue
    StringValue
    MoneyValue

    The label column is a string, but the other columns datatype is the same as the column name (IntValue is an int, etc)

    Now,  each row will have a label, but only 1 value for the other 5.  in other words, it will only have an intValue, or a DatetimeValue, etc...  a row will not have 2 values and a label.

    So here's my question.  Can I use COALESCE and convert it to a string?  I tried using this:

    CAST(COALESCE(IntValue, DecimalValue, DateTimeValue, StringValue, MoneyValue) AS varchar(50)) AS CustomValue

    It processes the first 3 or 4 rows then bombs with this error:

    Conversion failed when converting date and/or time from character string.


    is my format right?  if so, is the COALESCE coming across bad data?

    Thanks for your help as always!!!

    COALESCE() works by first looking down the parameter list, finding the highest data type in that list, and converting all of the other parameters to that type. In this case, you're going to have everything cast to a VARCHAR(n). In the second pass, the function then moves left to right until it finds the first non-null value in the converted parameter list..

    Can you tell us what you're trying to do? This is just a little too weird to be a real programming problem. My guess (why did you fail to post data?) are invalid and could not be cast.

    First, you missed an important keyword: PRECEDENCE.  It doesn't make sense to say the "highest data type".  It's the data type with the highest precedence.

    Second, VARCHAR has the LOWEST precedence in that list, not the highest.  DATETIME has the highest precedence in the list.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • meichmann

    SSCommitted

    Points: 1899

    Luis Cazares - Monday, July 30, 2018 1:32 PM

    meichmann - Monday, July 30, 2018 12:32 PM

    So here's my question.  Can I use COALESCE and convert it to a string?  I tried using this:

    CAST(COALESCE(IntValue, DecimalValue, DateTimeValue, StringValue, MoneyValue) AS varchar(50)) AS CustomValue

    Basically, you would need to do the opposite. You need to convert everything into a string and then use. Basically, you would need to do the opposite. You need to convert everything into a string and then use COALESCE.

    COALESCE(CAST( IntValue AS varchar(50))
            ,CAST( DecimalValue AS varchar(50))
            ,CAST( DateTimeValue AS varchar(50))
            ,CAST( StringValue AS varchar(50))
            ,CAST( MoneyValue AS varchar(50)))

    Luis,
         Thank you so much!  It worked like a charm!

    Drew,
         Thank you for the information.  I didn't know that there was a precedence in the COALESCE (learning stuff everyday!).

  • meichmann

    SSCommitted

    Points: 1899

    jcelko212 32090 - Monday, July 30, 2018 1:59 PM

    meichmann - Monday, July 30, 2018 12:32 PM

    Hello Everyone,
         I don't know if this is possible but I figured I would give it a try....

    I have a table with 6 columns:

    Label
    IntValue
    DecimalValue
    DateTimeValue
    StringValue
    MoneyValue

    The label column is a string, but the other columns datatype is the same as the column name (IntValue is an int, etc)

    Now,  each row will have a label, but only 1 value for the other 5.  in other words, it will only have an intValue, or a DatetimeValue, etc...  a row will not have 2 values and a label.

    So here's my question.  Can I use COALESCE and convert it to a string?  I tried using this:

    CAST(COALESCE(IntValue, DecimalValue, DateTimeValue, StringValue, MoneyValue) AS varchar(50)) AS CustomValue

    It processes the first 3 or 4 rows then bombs with this error:

    Conversion failed when converting date and/or time from character string.


    is my format right?  if so, is the COALESCE coming across bad data?

    Thanks for your help as always!!!

    COALESCE() works by first looking down the parameter list, finding the highest data type in that list, and converting all of the other parameters to that type. In this case, you're going to have everything cast to a VARCHAR(n). In the second pass, the function then moves left to right until it finds the first non-null value in the converted parameter list..

    Can you tell us what you're trying to do? This is just a little too weird to be a real programming problem. My guess (why did you fail to post data?) are invalid and could not be cast.

    As for you Joe, go away.  This is a real programming problem.  I was asking information about the COALESCE function and its operation, which didn't require DDL as you elegantly state with every post you make.  And, I might add, that posting DDL is not mandatory on this site.  Yes, it may help expedite the solution, it's not mandatory, as verified by a FORUM ADMIN.  Plus, there is nothing in the TOS about posting DDL based on http://www.sqlservercentral.com/About/Terms

    So do us all a favor and go away.

  • Gail Shaw

    SSC Guru

    Points: 1004484

    meichmann - Wednesday, August 1, 2018 5:58 AM

    Drew,
         Thank you for the information.  I didn't know that there was a precedence in the COALESCE (learning stuff everyday!).

    It's not a precedent in COALESCE, it's in implicit conversions as a whole, no matter where they occur.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • meichmann

    SSCommitted

    Points: 1899

    GilaMonster - Wednesday, August 1, 2018 6:07 AM

    meichmann - Wednesday, August 1, 2018 5:58 AM

    Drew,
         Thank you for the information.  I didn't know that there was a precedence in the COALESCE (learning stuff everyday!).

    It's not a precedent in COALESCE, it's in implicit conversions as a whole, no matter where they occur.

    Again, learning stuff everyday...thank you!

  • jcelko212 32090

    SSCrazy Eights

    Points: 9032

    drew.allen - Monday, July 30, 2018 2:08 PM

    jcelko212 32090 - Monday, July 30, 2018 1:59 PM

    meichmann - Monday, July 30, 2018 12:32 PM

    First, you missed an important keyword: PRECEDENCE.  It doesn't make sense to say the "highest data type".  It's the data type with the highest precedence.

    Second, VARCHAR has the LOWEST precedence in that list, not the highest.  DATETIME has the highest precedence in the list.

    Thanks for catching that. You just made me less ignorant 🙂 and reminded me that my mind is going in my old age. :crying:

    There's a chart in the ANSI/ISO standards, of which data types can be converted to others. I honestly can't remember if we use the word precedence or something else (compatibility? Convertibility?). The term precedence usually applies to operations, not casting.

    I don't know if they still teach the "my dear aunt Sally" for arithmetic operations (multiply, divide, add, subtract) in grade school, but that became especially important, when we got to floating-point numbers.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • tripleAxe

    SSCertifiable

    Points: 5605

    It's BIDMAS these days Joe - http://www.bbc.co.uk/bitesize/ks3/maths/number/order_operation/revision/2/

  • paul s-306273

    SSChampion

    Points: 10615

    jcelko212 32090 - Wednesday, August 1, 2018 8:27 AM

    drew.allen - Monday, July 30, 2018 2:08 PM

    jcelko212 32090 - Monday, July 30, 2018 1:59 PM

    meichmann - Monday, July 30, 2018 12:32 PM

    First, you missed an important keyword: PRECEDENCE.  It doesn't make sense to say the "highest data type".  It's the data type with the highest precedence.

    Second, VARCHAR has the LOWEST precedence in that list, not the highest.  DATETIME has the highest precedence in the list.

    Thanks for catching that. You just made me less ignorant 🙂 and reminded me that my mind is going in my old age. :crying:

    There's a chart in the ANSI/ISO standards, of which data types can be converted to others. I honestly can't remember if we use the word precedence or something else (compatibility? Convertibility?). The term precedence usually applies to operations, not casting.

    I don't know if they still teach the "my dear aunt Sally" for arithmetic operations (multiply, divide, add, subtract) in grade school, but that became especially important, when we got to floating-point numbers.

    BEDMAS, standing for Brackets, Exponents, Division/Multiplication, Addition/Subtraction.

  • jcelko212 32090

    SSCrazy Eights

    Points: 9032

    GilaMonster - Wednesday, August 1, 2018 6:07 AM

    meichmann - Wednesday, August 1, 2018 5:58 AM

    Drew,
         Thank you for the information.  I didn't know that there was a precedence in the COALESCE (learning stuff everyday!).

    It's not a precedent in COALESCE, it's in implicit conversions as a whole, no matter where they occur.

    Thank you!  That was the phrase I could not remember! I have to remember  that old age is better than death :satisfied:

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • RonKyle

    SSC-Dedicated

    Points: 31482

    drew.allen - Monday, July 30, 2018 2:08 PM

    jcelko212 32090 - Monday, July 30, 2018 1:59 PM

    meichmann - Monday, July 30, 2018 12:32 PM

    First, you missed an important keyword: PRECEDENCE. It doesn't make sense to say the "highest data type". It's the data type with the highest precedence.

    Second, VARCHAR has the LOWEST precedence in that list, not the highest. DATETIME has the highest precedence in the list.

    Thanks for catching that. You just made me less ignorant Smile and reminded me that my mind is going in my old age. Crying

    Well Joe sure took that well, but maybe saying "it would be better to say PRECEDENCE" could have been a little less confrontational.  I say often, including to myself, a lot of times how you say something is more important than what your saying.  Especially because it's not as if Joe were unclear.  He was simply missing the technical term.  Just one opinion...

  • Jeffrey Williams

    SSC Guru

    Points: 88659

    meichmann - Monday, July 30, 2018 12:32 PM

    Hello Everyone,
         I don't know if this is possible but I figured I would give it a try....

    I have a table with 6 columns:

    Label
    IntValue
    DecimalValue
    DateTimeValue
    StringValue
    MoneyValue

    The label column is a string, but the other columns datatype is the same as the column name (IntValue is an int, etc)

    Now,  each row will have a label, but only 1 value for the other 5.  in other words, it will only have an intValue, or a DatetimeValue, etc...  a row will not have 2 values and a label.

    So here's my question.  Can I use COALESCE and convert it to a string?  I tried using this:

    CAST(COALESCE(IntValue, DecimalValue, DateTimeValue, StringValue, MoneyValue) AS varchar(50)) AS CustomValue

    It processes the first 3 or 4 rows then bombs with this error:

    Conversion failed when converting date and/or time from character string.


    is my format right?  if so, is the COALESCE coming across bad data?

    Thanks for your help as always!!!

    If the goal is to build a concatenated string - there is a shortcut you can use as long as the values from the other columns are blank or null.

    CONCAT(Label, ' ', IntValue, DecimalValue, DateTimeValue, StringValue, MoneyValue) AS CustomValue

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

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

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