This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Introducing three new functions in SQL to make concatenating text easier
The CONCAT function makes it much easier to join columns together, while the STRING_AGG and CONCAT_WS functions make it easier to create things like comma-delimited lists.

Posted by Andy Brown on 20 September 2019

In this blog

Joining bits of text together
The CONCAT_WS function
The STRING_AGG function

Concatenating text in SQL, including delimited text

We've been teaching SQL for some years now - but wrongly, it seems! This blog shows how you can use the following 3 functions to join bits of text together:

Function	When introduced
CONCAT	SQL Server 2012
CONCAT_WS	SQL Server 2017
STRING_AGG	SQL Server 2017

We're particularly embarrassed by the CONCAT function, which makes it much easier to join things of different type together, and which has - it seems - been around for some years now!

Joining bits of text together

You can use the + key to join two bits of text together, but it can have unexpected results. Suppose you have a list of films:

The first few films in our table.

Suppose further that you want to create a description column, uniting the film name and genre name. You could do this using this expression:

-- join four bits of text together

Title + ' (' + Genre + ')' AS FilmDescription

To give you:

The description gives the film name followed by the genre in brackets.

However, you could also do this using the CONCAT function, which can join two or more bits of text together. Like this, in fact:

-- join four bits of text together

CONCAT(Title, ' (', Genre, ')') AS FilmDescription

This is probably clearer to read, but where the CONCAT function really comes into its own is when you include non-text values in your concatenation. For example, to get this:

For each film we show how many Oscars it won.

Using the + sign you would have to convert the integer field containing the number of Oscars to text first, to avoid an error:

-- film name with Oscars

Title + ' won ' + CAST(OscarWins AS varchar(10)) +

' Oscar(s)' AS FilmVerdict

However, the CONCAT function will implicitly convert any of its arguments to text if it can, which means that you could write the expression like this instead:

-- film name with Oscars

CONCAT(

Title,

' won ',

CAST(OscarWins AS varchar(10)),

' Oscar(s)'

) AS FilmVerdict

This function also dispenses with the need to convert null values, as these will be implicitly converted to empty strings. For example, suppose that you want to show each film with its budget in parentheses:

The final column shows for each film its title and budget (and where a budget is null, this appears as a blank string).

To create this column, you could use this expression:

-- film with budget

CONCAT(

Title,

' (',

BudgetDollars/1000000,

')'

) AS [Film with budget]

So you can unlearn the CAST, CONVERT, ISNULL and COALESCE functions! Well, at least for the purposes of string concatenation.

The CONCAT_WS function

This function was introduced in SQL Server 2017. The syntax is:

CONCAT_WS( Delmiiter to use, first value, second value, ... )

This allows you to create CSV files easily, among other things. For example, to create output like this:

Each row shows a film's title, the number of Oscars it won and the number of minutes it lasted.

You could use this query:

SELECT

CONCAT_WS(

-- the delimiter to use (a comma)

',',

-- one or more values to join together

Title, OscarWins, RunTimeMinutes

) AS FilmCsv

FROM

Film

WHERE

-- only show films winning more than 10 Oscars

OscarWins > 10

The STRING_AGG function

The syntax of this function is as follows:

STRING_AGG( What to join together, Delimiter to use)

It's especially useful for getting comma-delimited strings. For example, to show the list of films winning more than 10 Oscars:

The 3 films in the database winning more than 10 Oscars.

You could use this query:

SELECT

STRING_AGG(

-- what to join together

Title,

-- the delimiter to use (a comma)

','

) AS [Film list]

FROM

Film

WHERE

--only show films winning more than 10 Oscars

OscarWins > 10

The great thing about this is that you don't end up with another comma at the end of the list, which you then have to remove.

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Concatenating text in SQL, including delimited text

Joining bits of text together

The CONCAT_WS function

The STRING_AGG function

Head office

London

Manchester