CODEWITHSUNDEEP
sqlt-sqlconcatenation
Abdul
Abdul

Reputation: 2120

Concate Primary Keys in SQL

I want to concate Primary Keys of multiple tables in SQL directly. I used below query to concate three primary keys with a hyphen between them but the SQL 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 SQL Tables.

How it skipped the string part in query ?

Any help would be highly appreciated.

Updated

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.

Upvotes: 0

Views: 164

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270663

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.

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Try this

SELECT 5 + '-' +  8

The output is 13. I must admit, that I did not expect this...

And now try this

SELECT CAST('-' AS INT)

The result is 0. As your select starts with an INT, SQL Server tries to do a summa of int values. As the single hyphen is casteable to int implicitly, this returns the summa of your values...

The solution, as pointed out by others is either a cast of your column values to a string type or the usage of CONCAT

Upvotes: 1

Hynek Bernard
Hynek Bernard

Reputation: 744

I would need to see output, but I am assuming, some ID is stored as int and it is being counted, so you should use 

SELECT Cast(CID as Varchar(50)) + '-' + Cast(RID as Varchar(50)) + '-'+  Cast(CGID as Varchar(50)) As [IdCombination]

Upvotes: 0

Related Questions