This page has 0 threads | Add post

Concatenation in SQL

Written by Andrew Gould

In this tutorial

Concatenation in SQL
Using Variables in Concatenation
Converting Data Types to Text
Using Stored Procedures and Parameters
Creating Useful Stored Procedures

The key to dynamic SQL is concatenating strings to form valid SQL statements. This part of the series is designed to give you a brief primer on concatenation so feel free to skip to the next part if you already know how to do this!

Concatenation in SQL

To concatenate two strings of text in SQL you use the + symbol. The example below prints the result of concatenating two literal strings:

PRINT 'Today is ' + 'Tuesday'

The fairly unremarkable result of executing the above code is shown below:

The two strings are joined together into a single phrase.

The technique becomes more useful when one or more of the individual strings is provided by a variable component, such as a function:

PRINT 'Today is ' + DATENAME(DW,GETDATE())

The above code calculates the name of the current day of the week and concatenates it with a literal string. The result of executing the code depends on which day it is run.

Using Variables in Concatenation

When concatenating long strings it can be useful to use variables to store the various components of the phrase before joining them together.

DECLARE @DayName AS VARCHAR(9)

SET @DayName = DATENAME(DW,GETDATE())

PRINT 'Today is ' + @DayName

Converting Data Types to Text

When you're concatenating a phrase you must make sure that each bit of the sentence is a piece of text. The example below attempts to concatenate an integer with a literal string:

DECLARE @TheNumber AS INT

SET @TheNumber = 10

PRINT 'The number is ' + @TheNumber

Attempting to execute this code results in the following error message:

SQL has attempted to convert our literal string into a number - what we actually want to do is convert the number into a string.

You can use the CAST or CONVERT function to change one data type into another. The example below uses CAST:

DECLARE @TheNumber AS INT

SET @TheNumber = 10

PRINT 'The number is ' + CAST(@TheNumber AS VARCHAR(2))

If you prefer you can use the CONVERT function instead:

DECLARE @TheNumber AS INT

SET @TheNumber = 10

PRINT 'The number is ' + CONVERT(VARCHAR(2),@TheNumber)

Using Stored Procedures and Parameters

You're most likely to build dynamic SQL statements using a stored procedure into which you pass parameters. The code below creates a simple stored procedure which accepts a single parameter whose value is concatenated into a string:

CREATE PROC spTodayMessage

(

@Day AS VARCHAR(9)

)

BEGIN

PRINT 'Today is ' + @Day

END

After the code is executed to create the stored procedure it can be used to generate a message as shown below:

EXEC spTodayMessage 'Wednesday'

Creating Useful Stored Procedures

Now that you know the basics of concatenation in SQL the next part of this series will show you how to create useful stored procedures which will construct and execute dynamic SQL statements.

Previous
tutorial

Triggers

Previous
part

Dynamic SQL

Top of page

Next
part

Building Dynamic SQL Statements

Next
tutorial

Transactions

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Dynamic SQL

Concatenation in SQL

Concatenation in SQL

Using Variables in Concatenation

Converting Data Types to Text

Using Stored Procedures and Parameters

Creating Useful Stored Procedures

Head office

London

Manchester