New String Function In SQL Server 2017

Introduction

 

SQL Server 2017 introduced four new string functions as following.

• CONCAT_WS
• STRING_AGG
• TRANSLATE
• TRIM

SQL Server 2016 is introduced 2 new string function as following.

• STRING_ESCAPE
• STRING_SPLIT

I will demonstrate how we can use these functions and where we can use these functions in this article.

 

CONCAT_WS

 

Concatenates an argument with delimiter that was specified in the first argument. It returns string and its length is depending on the input.

 

Syntax

CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )

Function Arguments

Arguments	Description
separator	It is expression of any character set
argument1, argument2, … , argumentN	Expression of any type

 

Example

Select CONCAT_WS('|', 'Test 1', 'Test 2', 'Test 3', 5)

 

 

It requires one separator argument and a minimum of two arguments otherwise it raises an error. All the arguments are implicitly converted to string types and then they are concatenated.

 

 

  

It ignores the CONCAT_NULL_YIELDS_NULL setting. If the argument is null, it considers empty string of type varchar(1). It means, null values are ignored during concatenation and it does not added the separator. This feature is very useful when  concatenting strings which might have null value. For example Address fields, Address Line 2, address line 3 might have null value.

Select CONCAT_WS('|', 'Test 1', NULL, NULL, 'Test 4')

 

STRING_AGG

 

It concatenates the values of string expression and put separator value between them. The separator does not added at the end of string.

 

Syntax

STRING_AGG ( expression, separator )

Function Arguments

Arguments	Description
Separator	It is expression of VARCHAR / NVARCHAR type and used to concatenated strings.
Expression	Expression of any type. They are converted in VACHAR / NVARCHAR types during concatenation. Non string type is converted into NVARCHAR type

  

The return type is depending on first argument. If input argument is NVARCHAR / VARCHAR, return type will be the same as input and if input type is non-string, then return type will be NVARCHAR.

 

For example, if I have employee table and I want FirstName column record in comma separated value, we can use STRING _AGG function.

SELECT STRING_AGG (FirstName, ',') AS FirstNames FROM Employee;

 

This function is very useful, when we require any character separated value with GROUP BY clause.

SELECT FirstName, LastName, STRING_AGG(DepartmentName, ',') Departments  FROM Employee e
INNER JOIN EmployeeDepartments ed on ed.EmployeeId = e.Id
INNER JOIN Departments d on d.Id = ed.DepartmentId
Group by FirstName, LastName

 

 

TRANSLATE

 

It replaces some character set of input string with destination set of characters. It returns string.

 

Syntax

TRANSLATE ( inputString, characters, translations)

Function Arguments

Arguments	Description
Inputstring	It is string with any character type (VARCHAR, CHAR, NVARCHAR, NCHAR)
Characters	It is any character type and contains characters set those should be replaced
Translations	It is any character type and It matches with second argument by type and length

 

This function returns error if characters and translations have different length. It returns input string without any change in input if null vales are provided as characters or replacement arguments. The behaviour of this function is identical to the REPLACE function. It is equivalent to multiple REPLACE functions.

 

Example

Declare @Original VARCHAR(50) = '5*[6-4]/{8+2}'
Declare @Changed VARCHAR(50)
SET @Changed = TRANSLATE(@Original, '[]{}', '()()');
SELECT @Original as Original,@Changed as Changed

 

 

TRIM

 

It removes the space character or specified character from input string at the beginning and end of a string. It returns character expression with same type of input string. It returns NULL if the input string value is NULL. It is equivalent to LTRIM(RTRIM(@inputstring)). The behaviour with specified characters of this function is the same as the behavior of the REPLACE function where character from beginning and end are replaced with an empty character.

 

Syntax

TRIM ( [ characters ] FROM  string )

Function Arguments

Arguments	Description
Characters	It contains the character that we want to remove from input string.
Inputstring	It is any non-LOB character type such as NVARCHAR, NCHAR, VARCHAR, CHAR. The types NVARCHAR(MAX) and VARCHAR(MAX) are not allowed. It is input string where the characters need to be removed.

 

Example

 

Following example removes the space character from input string at the beginning and end.

SELECT TRIM('    Jignesh Trivedi  ')

 

Following example removes specified characters from both sides of string

SELECT TRIM('#@!.' FROM '#." Jignesh Trivedi ".')

 

 

STRING_ESCAPE

 

This function is introduced in SQL Server 2016. It escapes special characters and returns text with escaped character. It returns nvarchar(max) text with escaped special and control characters. Currently it can only escape JSON special characters.

 

Syntax

STRING_ESCAPE( text , type )

Function Arguments

Arguments	Description
text	It is input string that should be escaped
type	The type on which escaping rules will be applied. Currently the value supported is 'json'.

 

Example

 

In the following example, JSON text escapes the special character.

DECLARE @str VARCHAR(100) = '{ "Id" : "1", "Name" : "Jignesh Trivedi" }'
select STRING_ESCAPE(@str,'json')

 

 

STRING_SPLIT

 

This function is introduced with SQL server 2016. It splits the string with specified separator. This function is available only under compatibility level 130.

 

Syntax

STRING_SPLIT ( string , separator )

Function Arguments

Arguments	Description
string	It is input string with any character type
separator	It is single character that is used as separator.

 

It returns a single column table. The name of the column is "Value" and its type is NVARCHAR if any input arguments are either nvarchar or nchar else return VARCHAR.

 

Example

 

In the following example, I have split comma separated value

DECLARE @str VARCHAR(100) = 'Jignesh,Tejas,Rakesh'

SELECT VALUE FROM STRING_SPLIT(@str,',')

 

 

In the following example, I have Split comma separated value and joined split value with other table.

DECLARE @str VARCHAR(100) = 'Jignesh,Tejas,Rakesh'
SELECT e.* FROM [dbo].[Employee] e
INNER JOIN STRING_SPLIT(@str,',') f ON e.FirstName = f.Value

 

Summary

 

These newly introduced functions are very useful.

• CONCAT_WS - it concatenates variables into a single string using the first argument as separator
• TRANSLATE - it replaces character set in input string with specified character set provided in second argument
• TRIM - it removes the space charater or other specified character from beginning and ending of string
• STRING_AGG - It concatenates the values of string expression and puts separator value between them. The separator is not added at the end of string.
• STRING_ESCAPE - It is introduced in SQL Server 2016. It escapes special characters and returns text with escaped character.
• STRING_SPLIT - It is introduced with SQL server 2016. It splits the string with specified separator.