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?

Why do you need to store this? Does Number always have 7 digits (and if not how will you know if 12345622 is 123456,22 or 1234562,2) — Martin Smith
– Martin Smith, Commented Jun 3, 2013 at 16:34

Taryn · Accepted Answer · 2013-06-03 16:49:03Z

11

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

edited Jun 3, 2013 at 16:49

answered Jun 3, 2013 at 16:34

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2448666 Over a year ago

That was easy, thanks. Any clue about how to update my table at the same time? When I use the update statement errors occur because of the select statement

Taryn Over a year ago

@user2448666 you will just use an UPDATE statement, see my edit with demo

user2448666 Over a year ago

I had to change int to numeric, but this worked perfectly. Thanks for the help.

Collectives™ on Stack Overflow

SQL server: concatenating values from two numeric columns

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related