SQL Server 2016, 2017 and 2019 New Functions

There are several string and analytical functions introduced with the newer versions of SQL Server and are listed below.

STRING_SPLIT

is a table-valued function introduced in SQL Server 2016 (13.x) that splits a string into rows of sub-strings, based on a specified separator character.

Discussed about its functionality and usage in my previous blog. Please click here for details

APPROX_COUNT_DISTINCT

This function introduced in SQL Server 2019 (15.x) returns the approximate number of unique non-null values in a group.

Discussed about its functionality and usage in my previous blog. Please click here for details and here.

STRING_AGG

This function is introduced in SQL Server 2017 (14.x) Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.

It is equivalent to MySQL’s GROUP_CONCAT and Oracle's LISTAGG function.

Discussed about its functionality and usage in my previous blog. Please click here for details

CONCAT_WS 

This function is introduced in SQL Server 2017 (14.x); it returns a string resulting from the concatenation, or joining, of two or more string values in an end-to-end manner. It separates those concatenated string values with the delimiter specified in the first function argument. (CONCAT_WS indicates concatenate with separator.)

It requires 3 to 254 arguments to be passed.

Example:

SELECT CONCAT_WS(',','One','Two','Three','Four')

Result: One,Two,Three,Four

TRIM

This function is introduced in SQL Server 2017 (14.x); it eliminates the spaces in a given string. 

SELECT TRIM('       exampleText     ');

Prior to this version, there is the following way to trim the spaces. 

SELECT LTRIM(RTRIM('     exampleText    ');

DROP TABLE IF EXISTS  

In spite of the fact that there are some workarounds and strategies to drop the database objects if exists, this is the easy and direct approach. This is introduced in SQL Server 2016 (13.x).

DROP TABLE IF EXISTS TableName;  

In the earlier versions the following is approach.

-- To drop the temporary table

IF OBJECT_ID(N'tempdb..#temp1', N'U') IS NOT NULL   

DROP TABLE #temp1;  

GO

-- To drop the permanent table

IF OBJECT_ID('dbo.Emp', 'U') IS NOT NULL 

  DROP TABLE dbo.Emp; 

GO

Hope you find this article helpful.