Functions manipulating groups of strings

Functions for Manipulating Data in SQL Server

Ana Voicu

Data Engineer

CONCAT() and CONCAT_WS()

CONCAT(string1, string2 [, stringN ])

CONCAT_WS(separator, string1, string2 [, stringN ])

Keep in mind: concatenating data with functions is better than using the "+" operator.

CONCAT() and CONCAT_WS() example

SELECT 
    CONCAT('Apples', 'and', 'oranges') AS result_concat,
    CONCAT_WS(' ', 'Apples', 'and', 'oranges') AS result_concat_ws,
    CONCAT_WS('***', 'Apples', 'and', 'oranges') AS result_concat_ws2;

| result_concat    | result_concat_ws   | result_concat_ws2      |
|------------------|--------------------|------------------------|
| Applesandoranges | Apples and oranges | Apples***and***oranges |

STRING_AGG()

STRING_AGG(expression, separator) [ <order_clause> ]

Concatenates the values of string expressions and places separator values between them.

STRING_AGG() example

SELECT 
    STRING_AGG(first_name, ',') AS list_of_names
FROM voters;

| list_of_names                            |
|------------------------------------------|
| Carol,Ana,Melissa,Angela,Grace,Melody... |

SELECT 
    STRING_AGG(CONCAT(first_name, ' ', last_name, ' (', first_vote_date, ')'), CHAR(13))  AS list_of_names
FROM voters;

| list_of_names                    |
|----------------------------------|
| Carol Rai (2015-03-09)           |
| Ana Price (2015-01-17) ...       |

STRING_AGG() with GROUP BY

SELECT 
    YEAR(first_vote_date) AS voting_year,
    STRING_AGG(first_name, ', ') AS voters
FROM voters
GROUP BY YEAR(first_vote_date);

| voting_year | voters                           |
|-------------|----------------------------------|
| 2013        | Melody, Clinton, Kaylee,...      |
| 2014        | Brett, Joe, April, Mackenzie,... |
| 2015        | Cedric, Julie, Sandra,...        |
| 2016        | Isabella, Vincent, Haley,...     |

STRING_AGG() with the optional <order_clause>

SELECT 
    YEAR(first_vote_date) AS voting_year,
    STRING_AGG(first_name, ', ') WITHIN GROUP (ORDER BY first_name ASC) AS voters
FROM voters
GROUP BY YEAR(first_vote_date);

| voting_year | voters                         |
|-------------|--------------------------------|
| 2013        | Amanda, Anthony, Caroline,...  |
| 2014        | April, Brett, Bruce, Carl, ... |
| 2015        | Abigail, Alberto, Alexa,...    |
| 2016        | Barbara, Haley, Isabella,...   |

STRING_SPLIT()

STRING_SPLIT(string, separator)

Divides a string into smaller pieces, based on a separator.
Returns a single column table.

SELECT * 
FROM STRING_SPLIT('1,2,3,4', ',')

| value |
|-------|
| 1     | 
| 2     | 
| 3     | 
| 4     |

Let's practice!

Functions for Manipulating Data in SQL Server