Post
Ask Question

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
  1. 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
  1. 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.
  1. 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
  1. 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.
  1. SELECT STRING_AGG (FirstName, ',') AS FirstNames FROM Employee;  
 
This function is very useful, when we require any character separated value with GROUP BY clause.
  1. SELECT FirstName, LastName, STRING_AGG(DepartmentName, ',') Departments  FROM Employee e  
  2. INNER JOIN EmployeeDepartments ed on ed.EmployeeId = e.Id  
  3. INNER JOIN Departments d on d.Id = ed.DepartmentId  
  4. Group by FirstName, LastName  
 
 
TRANSLATE
 
It replaces some character set of input string with destination set of characters. It returns string.
 
Syntax
  1. TRANSLATE ( inputString, characters, translations)  
Function Arguments

ArgumentsDescription
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
  1. Declare @Original VARCHAR(50) = '5*[6-4]/{8+2}'   
  2. Declare @Changed VARCHAR(50)  
  3. SET @Changed = TRANSLATE(@Original, '[]{}', '()()');  
  4. 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
  1. TRIM ( [ characters ] FROM  string )  
Function Arguments

ArgumentsDescription
CharactersIt contains the character that we want to remove from input string.
InputstringIt 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.
  1. SELECT TRIM('    Jignesh Trivedi  ')  
 

Following example removes specified characters from both sides of string
  1. 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
  1. STRING_ESCAPE( text , type )  
Function Arguments

ArgumentsDescription
textIt is input string that should be escaped
typeThe 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.
  1. DECLARE @str VARCHAR(100) = '{ "Id" : "1", "Name" : "Jignesh Trivedi" }'  
  2. 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
  1. STRING_SPLIT ( string , separator )  
Function Arguments

ArgumentsDescription
stringIt is input string with any character type
separatorIt 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
  1. DECLARE @str VARCHAR(100) = 'Jignesh,Tejas,Rakesh'  
  2.   
  3. SELECT VALUE FROM STRING_SPLIT(@str,',')  
 
 
In the following example, I have Split comma separated value and joined split value with other table.
  1. DECLARE @str VARCHAR(100) = 'Jignesh,Tejas,Rakesh'  
  2. SELECT e.* FROM [dbo].[Employee] e  
  3. 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.


Similar Articles