I want to concate
SELECT CID + '-' + RID + '-'+ CGID As [IdCombination] ...
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
SELECT CONCAT(CID, '-', RID, '-', 'CGID) As [IdCombination]
CONCAT() knows that everything should be a string.