SQL Server: Combining Values ​​from Two Numeric Columns

Question

I am using SQL Server 2008. I created a new numeric column called unique_number , and I would like to populate this column by combining two other numeric columns called site and number . The site column ranges from 1-31, and values ​​less 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 + , I was able to get unique_number as the sum of two columns, but I need concatenation, not summation. I tried other sentences using cast as varchar , but the select statement continues to give me errors. Is there any reasonable way to do this?

Answer 1

You should use both columns as varchar and then combine 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, you will simply use the above in the update statement:

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

Watch Demo