New T-SQL Functions Introduced in SQL Server 2017

SQL Server 2017 is feature rich product that has been launched with multiple new features. You can read attached article where I have explained Top 10 new features added in SQL Server 2017 database engine. I have also explained about new T-SQL functions introduced in SQL Server 2017 (CONCAT_WS, TRANSLATE, TRIM, STRING_AGG) in attached article. Here, I will explain these new T-SQL functions with examples and their use cases.

New T-SQL Functions in SQL 2017

There are four new T-SQL functions introduced in SQL Server 2017. These are very useful that make SQL Server developers life easier. Let’s have a look at each one of these SQL 2017 new  functions with their examples and use cases.

CONCAT_WS

If you look at the name of this function CONCAT_WS then it will come out as concatenate with separator. This function requires a separator specified as 1st argument and minimum of two or more arguments mentioned as remaining arguments. All arguments will be concatenated into a single string with a separator specified in the 1st argument. Null values are ignored during concatenation, and does not add the separator.

Below are the examples of this new function. Suppose, you want to gather login details that has sysadmin access on your SQL Server Instance in a separator format then you can use below command to get these details. You can change the columns of this command or you can also change the T-SQL command if you want to gather some other information. You can also change the separator comma mentioned in first argument to some other separator as per your wish.

SELECT CONCAT_WS (‘,’, name, status, sysadmin) As LoginDetails from syslogins

The output of above command will look like below details.

LoginDetails

Sa,9,1
techyaz\test1,10,1
techyaz\deo1,9,0
ty1,9,0

As I said above, this function skips the null values. You can see this by running below command on your SQL Server 2017 instance.

SELECT CONCAT_WS(',',132, NULL, NULL, 'Urban Estate', 'Gurgaon', 122001) AS Address; 

Output of above command will be:

Address

132,Urban Estate, Gurgaon, 122001

You can see null values have been skipped in above output.

TRANSLATE

Another useful T-SQL function introduced in SQL Server 2017 is TRANSLATE. This function returns a string given in first argument after some characters given in second argument translated with the characters given in third arguments.

Here, you need to give three inputs. Input string is first argument that we want to modify with some changes. Second Argument is characters that is an expression of any character type containing characters that should be replaced. Third argument is an expression that will replace second argument mentioned in Inputstring. Make sure that the length and type of characters and translations will be same otherwise you will end up with errors. The output will be same as given in input string if you pass NULL value in any of the second or third argument.

Below is the example of this function.

SELECT TRANSLATE ('[7.124, 9.6]' , '[,]', '( )') AS Values,

Here we are replacing a square bracket having comma with regular bracket and blank space in place of comma.   Output of above T-SQL will be given below.

Values

(7.124 9.6)

You can see square bracket has been replaced with the given one and comma separator is also removed from above expression. If you want to revert it of you want to enter comma or any separator with changing the give brackets you can also get this done using this function.

SELECT TRANSLATE('(7.124 9.6)' , '( )', '[,]') AS Values;

Output of above command will look like below information.

Values

[7.124,9.6]

Another example of this function is given below. Run below t-sql command if you want to replace few to the brackets with the given in transactions argument.

SELECT TRANSLATE('[4+2]/{6-2}', '[]{}', '()()') As Formula;

The output of above command will be look like below info.

Formula

(4+2)/(6-2)

TRIM

This function has enabled users to remove the space characters or other characters from the start or end of the string. Earlier we used to do the same thing using LTRIM(RTRIM(@string)) T-SQL command.

Below example will remove the free spaces given before and after the word techyaz.

SELECT TRIM( '             techyaz              ') AS Result;

We can the output of above t-sql function given as:

Techyaz

Earlier we used to do the same thing using below t-sql command.

LTRIM(RTRIM('    techyaz    '))

STRING_AGG

Many developers were expected this function to be part of SQL Server since long time. Now their wish have been completed in SQL Server 2017. STRING_AGG is going to become very popular among all new functions. This function concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. Here is the example where we will see output from a table in a given format.

SELECT STRING_AGG(CONCAT(FirstName, ' ', LastName, ' (', ModifiedDate, ')'), CHAR(13))
AS names
FROM Person.Person;

Output of above command will look like:

Names

Manvendra Singh (Feb 8 2018 12:00AM)
Maruti Nandan (Dec 24 2017 12:00AM)
Angelo M (Feb 5 2018 12:00AM)

You can see all column values are showing a single string in each row. Another aspect of this function is given in below example.

Suppose, you have a database having information about your blog or published articles. There are separate tables to save articles and their tags. Now, developers want to return one row per each article with all associated tags. We can use below query to get the output.

SELECT a.ArticleId, Title, STRING_AGG (tag, ',') as Tags
FROM dbo.Article AS a
LEFT JOIN dbo.ArticleTag AS t
ON a.ArticleId = t.ArticleId
GROUP BY a.articleId, title;

Output of above command will look like below details.

ArticleIdTitleTags
69How to Check IP Address in LinuxLinux,ip,rhel,howto
70How to Learn SQL Server DBALearning
71What is Pages and ExtentsPage,extent,datafiles,db

Here, I have described new T-SQL functions introduced in SQL Server 2017 with their examples. I hope you like this article. Please follow our Facebook page and Twitter handle to get latest updates.

Read More:

 

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:
Summary
Article Name
New T-SQL Functions Introduced in SQL Server 2017
Description
Read this article to learn about new T-SQL functions (CONCAT_WS, TRANSLATE, TRIM, STRING_AGG) introduced in SQL Server 2017. Here, I have explained these new T-SQL functions with examples and their use cases.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *