SQL Server 2017 Features

 1. TRANSLATE: It basically takes a string as input and then translates the characters with some new characters, see below Syntax:  

TRANSLATE ( inputString, characters, translations)

For Example : SELECT TRANSLATE ('4*{1+1}/[81-41]','[]{}','()()')

Output : 4*(1+1)/(81-41)

This function is work as REPLACE function. but TRANSLATE function is easier to use and also useful when we have to replace more than one value in the string.

2. CONCATE_WS:  It simply concats all input arguments with the specified input delimiter.

Syntax : CONCAT_WS ( separator, argument1, argument1, [argumentN]… )

For Example :

SELECT CONCAT_WS(',','Count', '1', '2', '3', '4' ) AS counter;

Output : nikhil, hardik, nilesh, sandip

3. TRIM:  It simply works as the C# trim function, removes all spaces from start and end of the string.

Syntax: TRIM(inputString)

For Example :

SELECT TRIM('     Samlpe demo    ') AS result;

Output : Samlpe demo

It removes only space from start and ends, not between the word and string.

4. String_AGG:  It will used to create single comma-separated string.

Syntax: String_AGG(column name,',')

For Example :

create table names ( [name] varchar(50) )go 

insert into names values ('1'),('2'),('3'),('4')

Here I have used stuff

select stuff((select ',' + [name] as [text()] 

from names for xml path('')),1,1,'')

Here I have used string_agg

select string_agg([name],',') from names

Output : 1,2,3,4