Here are some new T-SQL functions added to SQL Server 2017. I’m running the RC 2 version.
TRIM:
SQL Server has separate functions for removing spaces at the start (LTRIM) and end (RTRIM) of a string. 2017 adds the TRIM function to remove leading and trailing spaces together.
declare @Test varchar(20) = ' Test string '; select @Test as OriginalString, trim(@Test) as TrimmedString;
Result:
OriginalString TrimmedString
Test string Test string
CONCAT_WS:
CONCAT_WS stands for concatenate with separator. The first argument is a string that will be used as the separator. Any number of string arguments can follow and these string will be combined into one string, with the designated separator between each value.
select concat_ws(',', 'First', 'Second', 'Third', 'Fourth');
Result:
First,Second,Third,Fourth
TRANSLATE:
TRANSLATE can be thought of as an extension of REPLACE. We can define a list of characters that we want replaced in a string, and then a list of the characters to replace them with.
select translate('[Test string]', '[]', '()');
Result: (Test string)
TRANSLATE
STRING_AGG:
STRING_AGG is similar to CONCAT_WS, except it will concatenate values from a column instead of a list of values. The second argument is the string to use as the value separator. We can also use the WITHIN GROUP clause to specify the order of the items in the delimited list.
drop table if exists #Test;
create table #Test(Team varchar(20) not null primary key);
insert into #Test values('Falcons'), ('Saints'), ('Panthers'), ('Buccaneers');
select string_agg(Team, ':') from #Test;
select string_agg(Team, ':') within group(order by Team desc) from #Test;
Result:
Buccaneers:Falcons:Panthers:Saints
Saints:Panthers:Falcons:Buccaneers
Links:
SQL Server 2017 New Features
SQL Shack – New 2017 String Functions
SQL Rob 