New String functions in SQL Server 2017

New String functions in SQL Server 2017

Microsoft added some new T-SQL functions in SQL Server 2017. These new functions can make your work easy and increase readability of your T-SQL Code. I will be discussing 3 newly added String functions in SQL Server 2017.  New String functions in SQL Server 2017 are:

  • TRIM

CONCAT_WS Function

Concat_WS function is similar to the Concat function that exists since SQL Server 2012. Difference between concat and concat_ws is that concat_ws function is able to add a separator between each string value it concatenates.

This function takes a variable number of arguments and concatenates them into a single string using the first argument as separator. It requires a separator and a minimum of two arguments. All arguments are implicitly converted to string types and are then concatenated.

For example:

SELECT CONCAT_WS(',','Apple', 'Microsoft','Samsung', 'HTC', 'LG','Mi') AS TechCompanies;

This TSQL will produce following result:

Apple,Microsoft,Samsung,HTC, LG,Mi

Another example:

SELECT CONCAT_WS(',',address1, address2,city, state,zip,country) AS user_address
from someaddresstable where user_id=1


Microsoft Way,Redmond,WA,98052,United States


I personally think this function’s name is very misleading. It does not translate from one language to another. Instead, it does the work of several replace functions, simplifying some queries.

See this SQL Code:

SELECT REPLACE(REPLACE(REPLACE(REPLACE('2*[3+4]/{7-2}','[','('), ']', ')'), '{', '('), '}', ')')

This TSQL code which will produce ‘2*(3+4)/(7-2)’ as result, can be simplified using Translate function.

SELECT TRANSLATE('2*[3+4]/{7-2}', '[]{}', '()()')