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?

score 10 · Accepted Answer · answered Mar 18 '10 at 19:31

10

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.

answered Mar 18 '10 at 19:31

Dathan

7,266
3
27
46

1

I think NVARCHAR(MAX) is overkill!, they are INTs, so there is a max length and can't contain any strange unicode characters!!! so varchar(11) would be fine. – KM. Mar 18 '10 at 19:46
@KM Good point, particularly on the `VARCHAR` vs. `NVARCHAR`. The length is a lesser concern - as long as the total length of the row doesn't exceed the allowable row size (4K?), I believe there will be no practical difference between using MAX versus the 11 you suggest. – Dathan Mar 18 '10 at 19:53
if this is a computed column that is not PERSISTED, you will potentially do this calculation millions of times, depending on how many SELECTs you do on this column. I would think there is some overhead when doing string operations on (MAX) vs (n) columns. Why take the chance, the int can't be larger than varchar(11). – KM. Mar 19 '10 at 12:09
@KM Yeah, you're probably right. For short fields on a table (assuming the table's set to 'large text out of row = false'), there shouldn't be an appreciable difference in read and write time. But I couldn't find documentation relating to processing overhead, or how MAX fields are treated in query results. So I'd definitely go with your version - better safe than sorry. – Dathan Mar 22 '10 at 15:12

score 4 · Answer 2 · answered Mar 18 '10 at 19:32

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)

score 0 · Answer 3 · answered Mar 18 '10 at 19:31

0

simple:

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

answered Mar 18 '10 at 19:31

Paulo Santos

11,285
4
39
65

score 0 · Answer 4 · answered Mar 18 '10 at 19:32

0

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.

answered Mar 18 '10 at 19:32

BradC

39,306
13
73
89

2

i think it does not. It still adds the columns – OrElse Mar 18 '10 at 19:36
Ah, thats funny, but I think I know why. SELECT 4+'a'+6 returns a syntax error "Syntax error converting the varchar value 'A' to a column of data type int", so it must be doing the implicit conversion the wrong way (its converting '-' to an integer (0), then adding them all together) – BradC Mar 22 '10 at 14:10

score 0 · Answer 5 · answered Apr 23 '15 at 08:03

first create table in design mode

add 2 column as col1 and col2

add another column computedcolumn and set computed column property

enter image description here

Also you can use that following script

CREATE TABLE [dbo].[tbl](
[col1] [varchar](50) NOT NULL,
[col2] [varchar](50) NOT NULL,
[ComputedColumn]  AS ((CONVERT([varchar],[col1],(0))+'-')+CONVERT([varchar],[col2],(0)))

)

Computed column should result to string

5 Answers5

Linked