Computed column should result to string

Question

Here is a snap of my database.

Both col1 and col2 are declared as int.

My ComputedColumn currently adds the Columns 1 and 2, as follows...

col1  col2  ComputedColumn
1     2     3
4     1     5

Instead of this, my ComputedColumn should join the columns 1 and 2 (includimg the '-' character in the middle) as follows...

col1  col2  ComputedColumn
1     2     1-2
4     1     4-1

So, what is the correct syntax?

Answer 1

You're probably defining your computed column as col1+col2. Try CAST(col1 AS NVARCHAR(MAX))+'-'+CAST(col2 AS NVARCHAR(MAX)) instead.

Or if you prefer, you can replace NVARCHAR(MAX) with NVARCHAR(10) or a different length of your choice.

Answer 2

simple:

SELECT ComputedColumn = convert(varchar, col1) + '-' + convert(varchar, col2)
  FROM Table

Answer 3

create table TableName
(
    col1 int,
    col2 int,
    ComputedColumn as Convert(varchar, col1) + '-' + Convert(varchar, col2)
)

Bear in mind that if either value is null then the result of ComputedColumn will also be null (using the default collation and settings)

Answer 4

SELECT col1, col2, (col1 + '-' + col2) as ComputedColumn

"+" is both addition and the concatenation character. You could explicitly convert, but in this case, including the '-' in the middle should cause an implicit conversion.