SQL server: concatenating values from two numeric columns

Question

I am using SQL Server 2008. I have made a new numeric column called unique_number and I would like to populate this column by concatenating two other numeric columns called site and number. The site column ranges from 1-31 and the values smaller than 10 do not have a zero in front of them. I Would like the following

Number      Site   unique_number
1234567      2       12345672
3456789      26      345678926

Using the + I have been able to get unique_number to be the sum of the two columns, but I want a concatenation to occur, not a summation. I've tried other suggestions using cast as varchar, but the select statement continues to give me errors. Is there a reasonable way to do this?

Answer 1

You should be able to cast both of the columns as a varchar and then concatenate them:

select number, site, 
  cast(number as varchar(50)) 
  + cast(site as varchar(2)) unique_number
from yt;

See SQL Fiddle with Demo

If you want to update your table, then you would just use the above in an update statement:

update yt
set unique_number = cast(cast(number as varchar(50)) 
                          + cast(site as varchar(2)) as int);

See Demo