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

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

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:

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 film description

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:

Number of Oscars

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 film with budget

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:

Join film name and details

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:

Oscar-winning film list

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.

This blog has 0 threads Add post