August 26, 2014 at 3:13 am
Hello,
I'm trying to merge or concatenate 2 columns together.
one is alphanumeric and the other is numeric.
example: (to continue my dog breed example)
boxer 1
lab 2
poodle 11
normally, I'd use the "text" function combined with the concatenate function in excel to merge the two together and maintain continuity
example:
boxer01
lab02
poodle11
even without the text function, I'd use an IF statement:
If column B > 9 then "column B" else concatenate("0","column B")
How do I translate this in SQL?
I tried
case when column_b>9 then concat(column_a,column_b) else concat(column_a,'0',column_b)
but I get an error "int64"
what am I doing wrong?
August 26, 2014 at 3:20 am
Couple things first...
The concat function was only added to SQL Server in 2012. Are you using SQL Server 2012?
The error you mention (int64) is not a SQL error. How are you trying to run that and what's the complete error message?
Edit: Also, will that numeric column ever be over 99?
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
August 26, 2014 at 8:53 am
As Gail said, SQL Server won't show an error like that. SSIS might do that and you should specify that because the syntax is completely different.
If the number goes over 99, you'll have problems with the following method and you'll need to change it. Note how I'm including the data in a way that can be easily used by anyone.
DECLARE @test-2 TABLE(
dogbreed varchar(20),
someint int)
INSERT @test-2 VALUES
('boxer', 1),
('lab', 2),
('poodle', 11)
SELECT *,
dogbreed + RIGHT('0' + CAST(someint as varchar(2)), 2),
dogbreed + RIGHT( 100 + someint, 2)
FROM @test-2
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply