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 (nvarchar, varchar, nchar, 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 (nvarchar, varchar, nchar, 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!!
