How to Concatenate Non Numerical Columns in SQL Server 2008 R2

This tutorial will show how to use the string concatenation in SQL Server 2008 R2. The string concatenation uses a plus sign(+) operator to concatenate strings. It allows two strings to be combined by appending the contents of one to the end of the other. String concatenation is useful when wanting to combine columns for temporary outputs. This tutorial will combine two columns of a sports team, City and Name, to output the complete sports team name in a column called “Team.”

Setting Up

In this tutorial we will be using a Sports database with a table called “SportsTeams”. The first thing we need to do is create the Sports database. This is possible with the CREATE DATABASE statement followed by “Sports.”

PgSQL

1

CREATEDATABASESports;

Now that we have a table, we can insert data into it. This is possible with the INSERT INTO, SELECT, and UNION statements. The INSERT INTO statement specifies which table and columns to insert values into. The SELECT statement is the data being inserted into the table, and the UNION statement prevents duplicate values from being inserted into the table.

PgSQL

1

2

3

4

5

6

7

8

USESports

CREATETABLEdbo.SportsTeams

(

Namevarchar(30)not null,

Cityvarchar(30)not null,

TeamStatevarchar(30)not null

);

String Concatenation

Now we can start concatenating the data to retrieve and output the full names of the sports team. First we call the SELECT statement to select the City, Name, and TeamState columns. To concatenate strings in SQL, we use the plus sign(+) operator. Simply place a plus sign operator in between the City and Name columns to concatenate them. We then assign a column alias of “Team” to the concatenation. The final thing to do is to use the FROM statement to let SQL know that we are selecting from the SportsTeams table.

PgSQL

1

2

3

4

5

6

7

8

9

10

11

12

USESports

INSERTINTOdbo.SportsTeams(Name,City,TeamState)

SELECT'Marlins','Miami','Florida'

UNION

SELECT'Magic','Orlando','Florida'

UNION

SELECT'Buccaneers','Tampa Bay','Florida'

UNION

SELECT'Rams','St. Louis','Missouri'

UNION

SELECT'Suns','Phoenix','Arizona'

Output

Execute the query and a table with two columns, Team and TeamState, will be outputted. Notice how in the Team column, the whole sports team name is together. Concatenating the City and Team columns made this possible.

Thanks for reading and make sure to download the source files to get a better understanding of how the code works.