String_AGG
This new function helps to generate comma separated string from the contents of a column from several records in a single string value
It was previously possible through XML trick -
select stuff((select ‘,’ + [name] as [text()]
from names for xml path(”)),1,1,”)
from names for xml path(”)),1,1,”)
which will be easily converted to below using string_agg
select string_agg(cast(Name as varchar(Max)), ‘,’) From User
Trim
This new function has been requested for a lot of developers for a long time.
Removing the empty spaces in a string always demanded the use of two functions, like this:
SELECT RTRIM(LTRIM( ‘ test ‘)) AS Result;
This new function simplifies this task:
SELECT TRIM( ‘ test ‘) AS Result;
Translate
Translate does the work of several replace functions, simplifying some queries.
Using ‘Replace’ function the transformation would be like this:
select replace(replace(replace(‘[137.4, 72.3]’,‘[‘,‘(‘),‘,’,‘ ‘),‘]’,‘)’) as Point
Using the ‘Translate’ function the transformations becomes way simpler:
SELECT TRANSLATE(‘[137.4, 72.3]’ , ‘[,]’, ‘( )’) AS Point,
TRANSLATE(‘(137.4 72.3)’ , ‘( )’, ‘[,]’) AS Coordinates
IIF
The IIF function is another function Access supports. It accepts as first input a predicate, as second input an expression to return in case the predicate is true, and as third input an expression to return in case the input is false or unknown. Here’s an example for using the function:
SELECT IIF(1 = 2, 'a', 'b') AS iif_result;
iif_result ---------- b
CONCAT
The CONCAT function concatenates the input values into a single result string. The concatenation operator + yields a NULL on NULL input. The CONCAT function
converts NULL inputs to empty strings before concatenation.
FORMAT
The FORMAT function allows you to format an input value to a character string based on a .NET format string. You can optionally indicate a culture when relevant. As an example, the following code formats the result of the GETDATE() function using the format string 'd' (meaning, short date pattern), in one case using US English culture, and in another Japanese:SELECT FORMAT(GETDATE(), 'd', 'en-US') AS us, FORMAT(GETDATE(), 'd', 'ja-JP') AS jpSELECT FORMAT(productid, '0000000000') AS strproductid, productname FROM Production.Products;DROP IF EXISTS
One of the handiest T-SQL enhancements with SQL Server 2016 is the new DROP IF EXITS statement. It lets you check if an object exists before dropping it enabling you to skip wiring all those IF EXISTS blocks that we all write in out T-SQL scripts. You can see an example below.DROP TABLE IF EXISTS [dbo].[MyTable];STRING_SPLIT Function
STRING_SPLIT is another handy new function that makes it easy to parse strings based on a delimiter. The string can be a varchar, nvarchar, char, or nchar data type.DECLARE @string varchar(100) = 'Richard, Mike, Mark' SELECT value FROM string_split(@string, ',')FOR JSON Clause
Likewise, the FOR JSON clause was add to enable you to convert tabular data to JSON. Unlike XML which has a native data type, the new JSON support is enabled by using the NVARCHAR data type. You can see an example of using the new FOR JSON clause in the following listing:SELECT object_id, name FROM sys.tables FOR JSON PATHJSON Functions
- ISJSON -- The ISJSON function tests whether a string contains valid JSON.
- JSON_VALUE -- The JSON_VALUE function extracts a scalar value from a JSON string.
- JSON_QUERY -- The JSON_QUERY function extracts an object or an array from a JSON string.
- JSON_MODIFY -- The JSON_MODIFY function updates the value of a property in a JSON string and returns the updated JSON string.
OPENJON Function
The OPENJSON table-value function (TVF) queries JSON text and can locate an array of JSON objects or iterate through the elements of array and generate a rowset from the results. You can see an example of using the OPENJSON function below.DECLARE @JSON NVARCHAR(100) SET @json = N'[ null, "string", 1, [true, false], ["a","b","c"], {"obj1":"obj2"} ]'; SELECT * FROM OPENJSON( @json )
