Top string functions in SQL Server 2017

SQL Server 2017 has been in the talk for its many features that simplify a developer’s life. With the previous versions, developers had to write T-SQL, or user-defined functions using temporary tables, and build complex logic, just for string manipulation. In almost every database I work with, I see many user-defined functions for string manipulation and string aggregation.

This article outlines the basic concepts of how to use the new string function in SQL Server 2017 on a Linux machine.

The SQL Server 2017 CTP 1.1 contains various string function out of the box, a few of them being:

  • Support for the WITHIN GROUP clause for the STRING_AGG function.

A collection of new string function in SQL Server 2017, such as TRANSLATE, TRIM, CONCAT_WS, STRING_AGG are almost similar to the string functions of other relational databases engines.

As developers, we try to achieve results in simple and smart ways. The string functions available in SQL Server 2017 make the life of a developer much simpler.

So, let us get started, and see the usage of the available string functions!


Removes the space character char(32) or other specified characters from the start or end of a string.

As a SQL developer, you must have often come across a scenario where you had to remove the empty spaces at the beginning and the end of strings. To achieve that, you may have used the string functions, RTRIM and
LTRIM—you had to use both because SQL Server does not have a function which can trim leading or trailing spaces of a given string with a single function, even though TRIM() is a very popular function in many languages. The release of SQL Server 2017 comes with this new string function “TRIM”, which you can use to get rid of the leading and trailing spaces around any given string.

SQL Server 2017 SQL Server
Syntax TRIM ([characters FROM] string)
Characters could be a literal, variable, or a function call of any non-LOB character type (nvarcharvarcharnchar, or char) containing characters to be removed. nvarchar(max) and varchar(max) types are not allowed.
A string is an expression of any character type (nvarcharvarcharnchar, or char) from which characters should be removed.
or CLR

Let us consider an example string, from which we have to remove the leading and trailing spaces:

further reading

Happy Learning!!



About Prashanth Jayaram

DB Technologist, Author, Blogger, Service Delivery Manager at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek My Profile: jayaram/ Connect Me: Twitter @prashantjayaram GMAIL The articles are published in:
This entry was posted in SQL Server 2017, Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s