Prashanth Jayaram

Top string functions in SQL Server 2017

June 8, 2017 by

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 string function in SQL Server 2017 on a Linux machine.

The SQL Server 2017 CTP 1.1 contains various string function 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 string function in SQL Server 2017, such as TRANSLATE, TRIM, CONCAT_WS, STRING_AGG are almost similar to the string functions of other relational databases engines.

As developers, we try to achieve results in simple and smart ways. The 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 string functions!

TRIM

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 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”, which you can use 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 (nvarcharvarcharnchar, 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 (nvarcharvarcharnchar, 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   ’


This is how we have been trimming the leading and trailing spaces until SQL Server 2016. With SQL Server 2017, we could use the TRIM function to remove the leading and trailing spaces of a given string:


TRANSLATE

The TRANSLATE 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


With the TRANSLATE function, the process becomes much simpler than using REPLACE


Note: The second and third arguments of the built-in 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:

Using Concat_WS, the entire string manipulation is made very simpler


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.
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 function provides a simple interface to string manipulation.


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.


Conclusion

This article outlines the concept of advanced string functions in comparison with the old-school methods of string manipulation.

With SQL Server 2017, string manipulation is a lot easier. Developers need not juggle alternatives or maintain obsolete workarounds.

These built-in functions perform fairly better than the user-defined functions or T-SQL.

References


Prashanth Jayaram

Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application.

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
Prashanth Jayaram
1,033 Views