SQL Server 2017 has been in the talk for its many features that simplify a developer’s life. With the previous versions, developers had to write T-SQL, or user-defined functions using temporary tables, and build complex logic, just for string manipulation. In almost every database I work with, I see many user-defined functions for string manipulation and string aggregation.
This article outlines the basic concepts of how to use the new SQL Server string function in SQL Server 2017 on a Linux machine.
SQL Server 2017 CTP 1.1 contains various SQL string functions out of the box, a few of them being:
- CONCAT_WS, TRANSLATE, and TRIM
- Support for the WITHIN GROUP clause for the STRING_AGG function.
A collection of new SQL string function in SQL Server 2017, such as TRANSLATE, TRIM, CONCAT_WS, STRING_AGG are almost similar to the SQL string functions of other relational databases engines.
As developers, we try to achieve results in simple and smart ways. The SQL Server string functions available in SQL Server 2017 make the life of a developer much simpler.
So, let us get started, and see the usage of the available SQL string functions!
TRIM
The SQL trim function removes the space character char(32) or other specified characters from the start or end of a string.
As a SQL developer, you must have often come across a scenario where you had to remove the empty spaces at the beginning and the end of strings. To achieve that, you may have used the SQL Server string functions, RTRIM and
LTRIM—you had to use both, because SQL Server does not have a function which can trim leading or trailing spaces of a given string with a single function, even though TRIM() is a very popular function in many languages. The release of SQL Server 2017 comes with this new string function “TRIM”. You can use the SQL TRIM function to get rid of the leading and trailing spaces around any given string.
| SQL Server 2017 | SQL Server | |
| Syntax |
TRIM ([characters FROM] string) Characters could be a literal, variable, or a function call of any non-LOB character type (nvarchar, varchar, nchar, or char) containing characters to be removed. nvarchar(max) and varchar(max) types are not allowed. A string is an expression of any character type (nvarchar, varchar, nchar, or char) from which characters should be removed. |
CREATE FUNCTION dbo.TRIM(@str VAR-CHAR(MAX)) RETURNS VAR-CHAR(MAX) BEGIN RETURN LTRIM(RTRIM(@str)) END or CLR |
Let us consider an example string, from which we have to remove the leading and trailing spaces:
‘ SQLShack ’
|
1 2 3 4 5 6 |
DECLARE @Str NVARCHAR(MAX) SET @Str = ' SQLShack ' SELECT @Str OriginalString, RTRIM(LTRIM(@Str)) TrimmedString GO |
This is how we have been trimming the leading and trailing spaces until SQL Server 2016. With SQL Server 2017, we could use the SQL TRIM function to remove the leading and trailing spaces of a given string:
|
1 2 3 4 5 6 7 |
SELECT @@version SQLVersion GO DECLARE @Str NVARCHAR(MAX) SET @Str = ' SQLShack ' SELECT @Str OriginalString, TRIM(@Str) TrimmedString |
TRANSLATE
The TRANSLATE SQL string function allows us to perform a one-to-one, single-character substitution in a string. Until SQL Server 2016, we used use REPLACE function to replace each character or write UDF function.
| SQL Server 2017 | SQL Server | |
| Syntax |
TRANSLATE ( inputString, characters, translations) Arguments InputString is an expression of any character type (nvarchar, varchar, nchar, char). Characters is an expression of any character type containing characters to be replaced. Translations, is a character expression that matches second argument by type and length. |
REPLACE ( string_expression , string_pattern , string_replacement ) or UDF. |
Let us consider the following example:
Replace the special characters in a string with ‘1’
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SELECT @@version SQLVersion GO DECLARE @Str NVARCHAR(MAX) SET @Str = '{~~[##SQLShack##]~~}' SELECT @Str InputString, REPLACE ( REPLACE ( REPLACE ( REPLACE ( REPLACE ( REPLACE(@Str,'#','1'), '[','1' ), ']','1' ), '~','1' ), '{','1' ), '}','1' ) OutPutString |
With the TRANSLATE SQL string function, the process becomes much simpler than using REPLACE
|
1 2 3 4 5 6 7 8 |
SELECT @@version SQLVersion GO DECLARE @Str NVARCHAR(MAX) SET @Str = '{~~[##SQLShack##]~~}' SELECT TRANSLATE(@Str, '#[]{}~~', '1111111'); GO |
Note: The second and third arguments of the built-in SQL string function, TRANSLATE, must contain an equal number of characters.
Output
| InputString | OutPutString |
| {~~[##SQLShack##]~~} | 111111SQLShack111111 |
CONCAT_WS
CONCAT_WS() stands for Concatenate with Separator, and is a special form of CONCAT(). The first argument is the separator—separates the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments be. If the separator is NULL, the result is NULL.
In the following example, the separator hyphen (–) is used. The CONCAT_WS is similar to the existing CONCAT() function, which was introduced in SQL Server 2012. The function concatenates a number of arguments or string values.
The CONCAT_WS behavior can be emulated using the STUFF and the COALESCE functions in SQL Server.
COALESCE() accepts a series of values along with a value to use in the event that all items in the list are null.
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
| SQL Server 2017 | SQL Server | |
| Syntax |
CONCAT_WS (separator, argument1, argument1 [, argumentN]…) Separator is an expression of any character type (nvarchar, varchar, nchar, or char). argument1, argument2, etc. could be expressions of any type. | Using coalesce to handle null values and stuff for string manipulation |
One primary difference is that concat_ws() skips NULL arguments, but + does not. You could emulate this behavior with:
Select stuff((coalesce(‘-‘ + A, ”) + coalesce(‘-‘ + B, ”) + . . .
), 1, 1, ”)
Of course, this doesn’t convert the values to strings, as concat_ws() does. Therefore, a version that’s closer, could be something like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT @@version SQLVersion GO select top 10 stuff( (coalesce (' - ' + FirstName, '') + coalesce(' - ' + MiddleName, '') + coalesce(' - ' + LastName, '') ), 2, 1, ' ') , FirstName, MiddleName, LastName FROM [Person].[Person] |
Using Concat_WS, the entire string manipulation is made very simpler
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT @@version SQLVersion GO SELECT TOP 10 CONCAT_WS ( ' - ' , FirstName , MiddleName , LastName ) as FullName, FirstName, MiddleName,LastName FROM [Person].[Person] |
STRING_AGG
Aggregate functions compute a single result from a set of input values. With the prior versions of SQL, string aggregation was possible using T-SQL or CLR, but there was no inbuilt function available for string aggregation, with a separator placed in between the values.
With SQL Server 2017, built-in STRING_AGG functions are available for string aggregation.
| SQL Server 2017 | SQL Server | |
| Syntax |
STRING_AGG ( expression, separator [ <order_clause> ] ) <order_clause> ::= WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] ) Arguments Separator is an expression of NVARCHAR or VARCHAR type that is used as a separator for concatenated strings. It can be a literal or a variable. The Expression could be an expression of any type. Expressions are converted to NVARCHAR or VARCHAR types during concatenation. Non-string types are converted to NVARCHAR type. + is used to optionally specify order of concatenated results using the WITHIN GROUP clause: copy code WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] ) | Aggregate String Concatenation SQL Server Using stuff and XML Parsing, or UDF |
The following example shows how simple it is to group based on the name field, and generate a CSV. The same can be derived using available SQL functions. However, this readily available SQL string function provides a simple interface to string manipulation.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
CREATE TABLE #temp(id int,name char(3)) INSERT INTO #temp VALUES (1,'CD') ,(1,'AB') ,(2,'LM') ,(3,'BC') ,(3,'GH') ,(4,'KJ') ,(3,'AB') SELECT id, STRING_AGG (name, ',') AS data FROM #temp GROUP BY id; SELECT id, STRING_AGG (name, ',') WITHIN GROUP (ORDER BY name ASC) AS data FROM #temp GROUP BY id; DROP TABLE #temp |
The old way to do this was using a STUFF command along with an XML path statement, which could then show one row of data, with values separated by a comma. While this is achievable, it is rather confusing to read and understand. The new way to do this is by the string aggregate ( STRING_AGG) function as given above.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE #temp(id int,name char(3)) INSERT INTO #temp VALUES (1,'CD') ,(1,'AB') ,(2,'LM') ,(3,'BC') ,(3,'GH') ,(4,'KJ') ,(3,'AB') select distinct t1.id, STUFF((SELECT distinct ',' + t2.name from #temp t2 where t1.id = t2.id FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') data from #temp t1; DROP TABLE #temp |
Conclusion
This article outlines the concept of advanced SQL string functions in comparison with the old-school methods of string manipulation.
With SQL Server 2017, SQL string manipulation is a lot easier. Developers need not juggle alternatives or maintain obsolete workarounds.
These new built-in SQL Sever string functions perform generally better than the user-defined functions or T-SQL.
Table of contents
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021