SQL Server 2017 New Functions

Now SQL server 2017 (RC 1) is release in July 2017, with some best features, let’s see some newly introduced String functions:

1.  CONCAT_WS: Concatenates a variable number of arguments with a delimiter specified in the 1st argument.

Example:

SELECT CONCAT_WS(',','1 Microsoft Way', NULL, NULL, 'Redmond', 'WA', 98052) AS Address;

Output:

Address

--------------------------------------

1 Microsoft Way,Redmond,WA,98052

2.   TRANSLATE: Returns the string provided as a first argument after some characters specified in the second argument are translated into a destination set of characters.

Syntax: TRANSLATE ( inputString, characters, translations)

Example:

SELECT TRANSLATE('[137.4, 72.3]' , '[,]', '( )') AS Point,    TRANSLATE('(137.4 72.3)' , '( )', '[,]') AS Coordinates;

Output

--------------------------------------------

 (137.4 72.3)       [137.4,72.3]

3.  STRING_AGG: Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

EXAMPLE:

                           SELECT town, STRING_AGG (email, ';') AS emails

                             FROM dbo.Employee

                           GROUP BY town;

Output

-------------------------------------------------

Seattle syed0@adventure-works.com;catherine0@adventure-works.com;kim2@adventure-works.com