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:

  • CONCAT_WS, TRANSLATE, and TRIM
  • 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!

TRIM

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.
CREATE FUNCTION dbo.TRIM(@str VAR-CHAR(MAX))
RETURNS VAR-CHAR(MAX)
BEGIN
RETURN LTRIM(RTRIM(@str))
END
or CLR

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

further reading

https://www.sqlshack.com/top-string-functions-in-sql-server-2017/

Happy Learning!!

 

 

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s