Wednesday, November 23, 2011

Concatenate two fields in sql server

Sometime we need to concatenate two or more fields n show them in formatted manner.If all the fields are varchar then its not a big deal just use the technique given below.

SELECT [PartyNm]+' ( '+ [TempAdd] +', '+ [ContactNo]+' )' as name  FROM [dbo].[tbl_AccountMaster]

But you have to face problem if the fields are int type or containing numbers only.In this scenario it add values instead of concatenation.
 SELECT  [Unit_Id]+[BD_Id] as ID FROM [dbo].[MST_Unit]

So to overcome this issue you have to convert the field into varchar than try to concatenate,this will give you the desired result.

SELECT '('+CONVERT(varchar,[Unit_Id])+'-'+convert(varchar,[BD_Id])+')' as ID FROM [dbo].[MST_Unit]

If you will attempt to concatenate columns without conversion like this one,
SELECT '('+[Unit_Id]+'-'+convert(varchar,[BD_Id])+')' as ID FROM [dbo].[MST_Unit]
Then got the following error

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ' )' to data type int.

No comments:

Post a Comment