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.
