SQL CONCATENATE CONCAT

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.
Links to topics on this page

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).

Output:

ID_date
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'
BEGIN

 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)

END

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.

Use a WHILE LOOP in SQL

Creating WHILE loops in SQL is straightforward.

WHILE [this condition is true]

BEGIN

…. do this …

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

END

Concatenate in SQL with other SQL functions

SQL concatenate WITH SELECT and DISTINCT
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