Abdul Abdul - 1 year ago 46
SQL Question

Concate Primary Keys in SQL

I want to concate

Primary Keys
of multiple tables in
directly. I used below query to concate three primary keys with a hyphen between them but the
skipped the hyphen and sum up the primary keys and result in a single value.

SELECT CID + '-' + RID + '-'+ CGID As [IdCombination] ...

where CID , RID and CGID are the
Primary Keys
of three

How it skipped the
part in query ?

Any help would be highly appreciated.


For Example : The Values of CID , RID and CGID are 3 , 4, 3 respectively. It should be 3-4-3 but the result is 10.

Answer Source

What is happening? Remember that + means both addition and string concatenation. It so happens that - can be interpreted as a number (like -0), so SQL Server prefers to interpret the + as addition.

Normally, when you do this type of operation, the separation character cannot be interpreted as a number, and you just get an error. I am amused that you don't get an error in this case.

One method is to explicitly cast the values as strings:

SELECT CAST(CID as VARCHAR(255)) + '-' + CAST(RID +  as VARCHAR(255)) '-'+  CAST(CGID  as VARCHAR(255)) As [IdCombination] 

In SQL Server 2012, you can do this more simply using CONCAT():

SELECT CONCAT(CID, '-', RID, '-', 'CGID) As [IdCombination] 

CONCAT() knows that everything should be a string.