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:
DECLARE @C CHAR(10) = ' ABC ';
PRINT 'My new nice Trimming: "' + TRIM(@C) + '"';
GO
PRINT 'My new nice Trimming: "' + TRIM(@C) + '"';
GO
#2. String Concatenation.
How do you usually do string concatenation in T-SQL?Probably something like this:
DECLARE@A VARCHAR(10) = 'ABC';
DECLARE @B VARCHAR(10) = 'DEF';
DECLARE@C VARCHAR(10) = 'GHJ';
PRINT @A + ';' + @B + ';' + @C;
GO
DECLARE @B VARCHAR(10) = 'DEF';
DECLARE@C VARCHAR(10) = 'GHJ';
PRINT @A + ';' + @B + ';' + @C;
GO
Now you can do it another way, using function "CONCAT_WS":
DECLARE@A VARCHAR(10) = 'ABC';
DECLARE @B VARCHAR(10) = 'DEF';
DECLARE@C VARCHAR(10) = 'GHJ';
PRINT CONCAT_WS(';', @A, @B, @C);
GO
DECLARE @B VARCHAR(10) = 'DEF';
DECLARE@C VARCHAR(10) = 'GHJ';
PRINT CONCAT_WS(';', @A, @B, @C);
GO
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:
SELECT SUBSTRING(
(SELECT ', ' + name FROM master.sys.tables
FOR XML PATH(''))
,3,8000);
GO
(SELECT ', ' + name FROM master.sys.tables
FOR XML PATH(''))
,3,8000);
GO
Now you can aggregate your strings by using function "STRING_AGG":
SELECT STRING_AGG(name, ', ') FROM master.sys.tables;
Edit: 2017-01-26
Now you can aggregate your strings by using function "STRING_AGG" with ordering by certain field:
SELECT STRING_AGG(name, ', ') WITHIN GROUP ( ORDER BY object_id DESC)
FROM master.sys.tables;
FROM master.sys.tables;
#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.
PRINT TRANSLATE ( 'This is my unencrypted message',
'abcdefghijklmnopqrstuvwxyz',
'mnopqrstuvwxyzabcdefghijkl' );
GO
'abcdefghijklmnopqrstuvwxyz',
'mnopqrstuvwxyzabcdefghijkl' );
GO
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:
PRINT TRANSLATE ( 'ftue ue yk gzqzodkbfqp yqeemsq',
'mnopqrstuvwxyzabcdefghijkl',
'abcdefghijklmnopqrstuvwxyz' );
GO
'mnopqrstuvwxyzabcdefghijkl',
'abcdefghijklmnopqrstuvwxyz' );
GO
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.
