Concatenating of 2 columns

Question

I am using the following sample code to combine 2 columns which works for me

create table #tmpdata 
(account int not null
,panlast4 varchar(4) not null)
insert into #tmpdata values ('456789','1456')
select account,panlast4,(cast(account as varchar(10))+ ' ' + PANLAST4) as id
from #tmpdata

when I apply the same theory to the database I am working on it sums the 2 columns together.

I have checked the data types of the 2 columns of the database and the account is INT and the panlast4 is a string but from the reading I have done to get this to work I need to convert it to a string so I am a little confused by this.

Answer 1

Yes, if you have

 SELECT account, panlast, account + panlast4

You will get numeric addition.

SQL Server has a strict data-type precendence and whenever an expression in SQL Server contains operands of different types, SQL Server will convert the type with lower precedence will be converted to the other type. Or you get an error if there is no implicit conversion.

Varchar has lower precedence than int, and thus you get numeric addition.

I recommend that you always use explicit conversion, and don't rely on implicit conversion. (And in my opinion, it's a huge mistake that SQL Server has implicit conversion between strings and numbers.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Yes, if you have

 SELECT account, panlast, account + panlast4

You will get numeric addition.

SQL Server has a strict data-type precendence and whenever an expression in SQL Server contains operands of different types, SQL Server will convert the type with lower precedence will be converted to the other type. Or you get an error if there is no implicit conversion.

Varchar has lower precedence than int, and thus you get numeric addition.

I recommend that you always use explicit conversion, and don't rely on implicit conversion. (And in my opinion, it's a huge mistake that SQL Server has implicit conversion between strings and numbers.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Thanks Erland in the example I posted how would you use explicit conversion?

Answer 3

What you are doing in the above query is explicit conversion which is correct and it should work. Using CAST is the right way to it? I tried your example and it concatenated. Are you still not getting the concatenation with the CAST Operator?

Answer 4

What you are doing in the above query is explicit conversion which is correct and it should work. Using CAST is the right way to it? I tried your example and it concatenated. Are you still not getting the concatenation with the CAST Operator?

Right I see, I was reading Erlands response and was getting the impression I was doing it wrong.

I always use CAST as from reading it is ANSI standard. (also a lot more easier to write then CONVERT IMO)

Funnily enough I managed to get the query to work with the database I was using, so I must of missed something in my code.

So everything is good

Thanks for your help