Icon of SQL

To concatenate in SQL use the CONCAT() function.

SQL’s CONCAT function is used to combine table values, parameters, and/or custom string values.

For example, CONCAT can be used to create:

  • dynamic text (inserting dynamic values – e.g. max, min, average, sum – into a summary phrase / paragraph),
  • lookup’ columns by combining variables, and
  • readable user-friendly outputs.

Syntax for CONCAT in SQL

To concatenate in SQL, use the CONCAT function and add the objects to combine, separated by a comma.

SELECT CONCAT( [var], [parameter], ‘string’, [var] )

… where

  • [var] is a variable name,
  • [parameter] is a custom parameter (e.g. @start_date) and
  • string’ is any character or numeric string, wrapped in single quote marks.
  • The objects to join together are separated by a comma (,).
Example: SQL concatenate multiple variables and strings
SELECT CONCAT ( [OrderID], ' – ', [Date] ) as 'ID_date'
FROM [Table_1]

In the example above, two variables (OrderID and Date) are joined together, separated by a hyphen (with a space either side of the hyphen).


14560 – 15/06/2020
14561 – 16/06/2020
14562 – 17/06/2020

Example: Use SQL CONCAT with custom parameters

In this example below, we show how to use CONCAT() with SQL custom parameters (e.g. @start_date).

The example shows how to:

  • create a temporary table with columns in SQL;
  • load a series of date ranges into the temp table;
  • use a WHILE loop in SQL to repeat commands;
  • add dates to dates using SQL’s DATEADD() function.
drop table #temp_dates

declare @start_date date
declare @end_date date

set @start_date = '2020-03-01'
set @end_date = '2020-03-31'

CREATE table #temp_dates (
[Date range] varchar(100)

WHILE @start_date < '2020-03-31'

 insert into #temp_dates
 select concat(@start_date, ' to ', @end_date) as 'Date range'

 set @start_date = DATEADD(DAY, 7, @start_date)
 set @end_date = DATEADD(DAY, 7, @end_date)


select *
from #temp_dates

Output from above SQL query

Date range
2020-03-01 to 2020-03-31
2020-03-08 to 2020-04-07
2020-03-15 to 2020-04-14
2020-03-22 to 2020-04-21
2020-03-29 to 2020-04-28
Create a temporary table in SQL

Syntax to create a temp table: temp tables in SQL are prefixed with a ‘#’ hash symbol, followed by your custom name.

CREATE table #table_name (Column_1 datatype, Column_2 datatype, Column_3 datatype [ ... ])

Create custom parameters in SQL

A parameter in SQL is created using the @ symbol with the custom parameter name. e.g. @start_date. The parameter is initialized by the function DECLARE and assigned a data type, the initial value is assigned by the function SET, and the parameter value amended using SET again. See example in code above.


Creating WHILE loops in SQL is straightforward.

WHILE [this condition is true]


…. do this …

…. move on one repeat in the loop, unless condition is no longer true …


Concatenate in SQL with other SQL functions

SELECT CONCAT( 'Date last updated ', (select distinct max(Record_date) )

FROM [table_1]

In this example, the most recent date is extracted from the variable Record_date in table_1, and the distinct value returned.

Output: Date last updated 13/06/20

SQL concatenate with calculation functions
SELECT CONCAT (floor (DATEDIFF ( day, Date_1, Date_2) / 7 ),' Weeks and ', DATEDIFF( day, Date_1, Date_2), ' Days') as ‘Time lag’

The FLOOR() SQL function returns the largest integer value that is smaller than or equal to a number. This statement above returns the number of weeks and days between two dates, and returns the value as ‘N weeks and N days’.

Output: 3 Weeks and 4 Days