In the very beginning I want to warn you:
These new language features are not available in the SQL Server product yet. Only in Community Technical Preview. Moreover, the Community Technical Preview (CTP 1.1) is available for Linux Edition of SQL Server.
Official notes on the product: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-whats-new
So, here they are:
Four newest T-SQL features:
#1. Trimming.Count, how many times in your SQL programming life you've used the construction "RTRIM(LTRIM(...))" to get rid of empty spaces at the beginning and at the end of a string.
How many times you have wished to have a simple function "TRIM" to do the job?
And here it is:
#2. String Concatenation.How do you usually do string concatenation in T-SQL?
Probably something like this:
Now you can do it another way, using function "CONCAT_WS":
You might ask me "Why do we need this?". My guess would be "-To simplify the invasion."
In other words make a migration from MySQL to SQL Server less painful.
#3 String Aggregation.That is the really good one.
Since Microsoft introduced XML support in SQL Server, the most common string concatenation technique was use of "XML PATH('')" like this:
Now you can aggregate your strings by using function "STRING_AGG":
Now you can aggregate your strings by using function "STRING_AGG" with ordering by certain field:
#4 Translation.Microsoft introduced new T-SQL function "TRANSLATE", which simply replaces characters within a string. For a string you want to do a replacement for you have to specify two series of characters: one is "originals" and second is "replacements". Both these series must have equal length.
The result will be "ftue ue yk gzqzodkbfqp yqeemsq".
If we replace the original string by the result and switch second and third parameters we can get our original string as a result:
Looks awesome, isn't it?
However, the restriction that second and third parameters are must be same size makes this function not usable for conversion of text for XML, where we have to replace special 5 XML characters: "&'<">".
Also, if we want to keep consistency of capitalization, our database must be in case sensitive collation.