Converting a COALESCE statement to varchar

Question

Converting a COALESCE statement to varchar

meichmann

SSCommitted

Points: 1900
More actions
July 30, 2018 at 12:32 pm

#355050

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!!!

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

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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

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: 183706 More actions · Answer 2

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: 9303 More actions · Answer 3

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: 76985 More actions · Answer 4

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: 1900 More actions · Answer 5

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: 1900 More actions · Answer 6

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: 1004504 More actions · Answer 7

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: 1900 More actions · Answer 8

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: 9303 More actions · Answer 9

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: 5706 More actions · Answer 10

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

paul s-306273 SSChampion Points: 10730 More actions · Answer 11

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: 9303 More actions · Answer 12

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: 31556 More actions · Answer 13

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: 90303 More actions · Answer 14

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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