Geek Answers Handbook

SQL specific primary keys

I want to instantiate Primary Keys several tables in SQL directly. I used the query below to instantiate three primary keys with a hyphen between them, but SQL skipped the hyphen and summed the primary keys and resulted in a single value.

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

where CID, RID and CGID are Primary Keys from three SQL tables.

How did he skip the string part in the request?

Any help would be greatly appreciated.

Update

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

0
source share
3 answers

What's happening? Remember that + means both addition and concatenation of strings. It so happened that - can be interpreted as a number (for example, -0 ), so SQL Server prefers to interpret + as an addition.

Usually, when you perform this type of operation, the separation character cannot be interpreted as a number, and you just get an error message. It surprises me that in this case you will not get an error.

One method is to explicitly enter 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 make it simpler using CONCAT() :

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

CONCAT() knows that everything should be a string.

+1
source

try it

 SELECT 5 + '-' + 8

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

Now try this

 SELECT CAST('-' AS INT)

The result is 0. When your selection starts with INT, SQL Server tries to sum the int values. Since the only hyphen is implicit for int, this returns the sum of your values ​​...

The solution, as pointed out by others, is either listing your column values, or a string type, or using CONCAT

+1
source

I will need to see the output, but I assume that some identifier is stored as an int, and it is counted, so you should use

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

More articles:


All Articles