May 22, 2013 at 11:42 pm
HI,
I have a table, SampleNums, containing a couple of int columns that I need to concatenate into one column. When the columns are stored as varchar datatype my below ddl works, but when I recreate the SampleNums table with num1 and num2 as int columns, the concatenation doesn't work.
create table SampleNums
(
SomeID int
, num1 varchar(4) null
, num2 varchar(4) null
)
insert into SampleNums
values
(1, 858, 621),
(2, 597, 425),
(3, 683, 840),
(4, NULL, 333),
(5, 444, NULL);
select * from SampleNums
--drop table SampleNums;
select
SomeID
, NewNum = case
when cast(num1 as varchar(10)) is null then NUM2
when cast(num2 as varchar(10)) is null then NUM1
else cast(num1 as varchar(10)) + '.' + cast(num2 as varchar(4))
end
from SampleNums
---DESIRED OUTPUT
select '1', '858.621' UNION ALL
select '2','597.425' UNION ALL
select '3', '683.840' UNION ALL
select '4', '333' UNION ALL
select '5', '444'
but with everything the same, except that Num1 and Num2 have int datatypes (use drop ddl and run create sequence again with datatype int) I get this error:
--with datatype as int
Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '858.621' to data type int.
Also, if I recreate and change datatypes to numeric, I get the following output. It is strange that line 1 and 3 have increased in value by 1:
--with datatype as numeric
select '1', '859' UNION ALL
select '2','597' UNION ALL
select '3', '684' UNION ALL
select '4', '333' UNION ALL
select '5', '444'
I would like conditional statements to work regardless of int/numeric/varchar datatype. Why, when I am handling for the string concatenation using CAST is it breaking? How to fix? Thanks!!!!
May 23, 2013 at 12:03 am
The 858.621 value is not an int value should be cast with decimal or numeric.
May 23, 2013 at 12:19 am
sounds like a paraphrase of error message. I don't get your point.
There's an int I've cast as varchar so that I can concatenate with '.' and num2 which I've also cast as varchar..
If I missed some insight please elaborate.
May 23, 2013 at 12:23 am
Change your query like below
select
SomeID
, NewNum = case
when cast(num1 as varchar(10)) is null then cast(num2 as varchar(10))
when cast(num2 as varchar(10)) is null then cast(num1 as varchar(10))
else cast(num1 as varchar(10)) + '.' + cast(num2 as varchar(10))
end
from SampleNums
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 23, 2013 at 12:29 am
I mean when you change the datatype of num1 or num2 from varchar to int, it will not parse the value 858.621 because the int will not hold decimal places. so giving the error you mentioned.
What exactly you are trying to achieve, because your desired output is already coming from your first query. (i.e. num1 or num2 with varchar datatype)
May 23, 2013 at 6:50 am
Thank you Kingston. Missed converting those two column references to varchar.
May 23, 2013 at 6:52 am
Bhaskar, thanks for input. I think the problem in this case was that I am concatenating a string value ie. '.' with a different datatype and everything has to be the same data type before it can be concatenated.
I just didn't catch all the areas in which I needed to make the conversion, and Kingston pointed that out to me.
May 23, 2013 at 7:03 am
KoldCoffee (5/23/2013)
I think the problem in this case was that I am concatenating a string value ie. '.' with a different datatype and everything has to be the same data type before it can be concatenated.
No. That was not the problem.
You query previously looked like this
select
SomeID
, NewNum = case
when cast(num1 as varchar(10)) is null then NUM2 -- This gives an INT
when cast(num2 as varchar(10)) is null then NUM1 -- This again gives an INT
else cast(num1 as varchar(10)) + '.' + cast(num2 as varchar(4)) -- This will give a VARCHAR
end
from SampleNums
The column "NewNum" in your result can have only one data type( either INT or VARCHAR )
SQL Server tries to convert all values to INT based on precedence and errors out when it tries to convert the VARCHAR value '858.621' to INT data type.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply