1. Functions manipulating groups of strings
Welcome to the last video of this chapter.
The functions you are about to learn have been recently introduced in SQL Server (the 2016 and 2017 releases). They solve some old and quite common challenges encountered by developers, that required writing and working with custom functions, so I hope you are as excited as I am to find out about them.
2. CONCAT() and CONCAT_WS()
You can concatenate a series of strings using CONCAT() and CONCAT_WS().
The first function joins values together.
CONCAT_WS(), meaning CONCAT with separator, receives a character value as the first parameter, which is called "the separator". This value is introduced between the strings that are being concatenated.
The advantage of using functions instead of joining together values with the "+" operator is that you can concatenate all data types, not only strings. It is also less error-prone, since the result will always be a concatenation, while the "+" operator may actually do an addition if the values are not first converted to strings, leading to unexpected results.
3. CONCAT() and CONCAT_WS() example
This is how you can use these functions in a query.
With CONCAT(), you get all the strings linked together so if you want to have spaces between words, you need to take care of this manually.
CONCAT_WS() is more effective, since it gives you the possibility of choosing the separator between strings. In the first example I used spaces and the result looks very nice but you can use any separator you like, just as I am using the stars in the second example.
4. STRING_AGG()
STRING_AGG(), or string aggregate, is also used to concatenate a series of strings. The separator is placed between each string but not at the end.
It also has an optional clause, which I will show you on a practical example.
5. STRING_AGG() example
The first query is a simple example of how to use STRING_AGG().
The result is a list with all the first names, followed by comma.
The expression to be aggregated can be as complex as you want it to be.
In this second example, I concatenated the first and last names with the first voting date.
CHAR(13) is the carriage return character, and a list separated by this character will show values one below the other.
6. STRING_AGG() with GROUP BY
Another effective way for using this function is for concatenating values in groups.
In this example, I perform a group by the year of the first voting date.
Then, I group all the first names of the voters from that specific year.
7. STRING_AGG() with the optional <order_clause>
And the optional clause for this command can be used like this.
When you have a GROUP BY in your query, you can order your concatenated values based on a column. After the STRING_AGG function, you add the command: WITHIN GROUP and in parentheses an ORDER BY clause.
You can see here that the voters appear in alphabetical order in this table.
Creating a string from parts or breaking a string into parts are common tasks in SQL Server. STRING_AGG() helps with the first part and the next function helps with the second.
8. STRING_SPLIT()
STRING_SPLIT() is the opposite of STRING_AGG(): it splits a bigger string into pieces based on a given separator.
Because the result of the function is a table, it cannot be used as a column in the SELECT clause; you can only use it in the FROM clause, just like a normal table.
9. Let's practice!
You learn best by doing some exercises by yourself. This is what you are going to do now: apply the newly learned functions on some practical examples.