Concatenate Numbers using SQL

How do we concatenate numbers in sql?

Let us take an example for this, say; we create a table with two integer columns.

USE TempDB

GO

CREATE TABLE concatenate_integers ( col1 int, col2 int)

GO

Now let us put a couple of records over here on which we can run our analysis on.

INSERT INTO concatenate_integers

Values(1,2)

INSERT INTO concatenate_integers

Values(11,12)

INSERT INTO concatenate_integers

Values(21,31)

GO

For concatenation in SQL Server, we use the “+” operator.

So, as a test let’s see what’s going to happen if we are going to write a query

select Col1,Col2,Col1+Col2 as Conc from concatenate_integers

Col1       Col2       Conc

1              2              3

11           12           23

21           31           52

Oh, we got a sum!!!

So, for integer type columns the “+” operator returns sum.

But that’s not what we are looking for what we want as result is 12, 1112, 2131…

So, first we need to cast these columns as strings before we concatenate the numbers.

Here it goes…

select Col1,Col2,Cast(Col1 as varchar)+Cast(Col2 as varchar) as Conc from concatenate_integers

Col1       Col2       Conc

1              2              12

11           12           1112

21           31           2131

Learning: The + sign sums integer type columns but concatenates character type columns.

You can also try the same with the below code.

DECLARE @A INT

DECLARE @B INT

DECLARE @C VARCHAR

DECLARE @D VARCHAR

SET @A=1

SET @B=2

SET @C = 'A'

SET @D = 'B'

SELECT @A+@B

-- RESULT IS 3

SELECT @C+@D

-- RESULT IS AB

SELECT CAST(@A AS VARCHAR) +CAST(@B AS VARCHAR)

-- RESULT IS 12

Tagged as: SQL Queries