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?

Solution 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.

Solution 2

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)