MS SQL TRIM, LTRIM, RTRIM

TRIM

TRIM is a new MS SQL Server function introduced from MS SQL Server 2017.

Syntax:

TRIM ([characters FROM ]string)

  • String- An expression of any character type (nvarchar, varchar, nchar, or char) where characters should be removed.
  • Characters - A literal, variable, or function call of any non-LOB character type (nvarchar, varchar, nchar, or char) containing characters that should be removed. nvarchar(max) and varchar(max) types aren't allowed.

It removes characters from both sides (at the beginning/left and at the end/right) of the given string value. 

Before introduction of TRIM function in MS SQL Server 2017 it was possible to accomplish this task by using the combination of both LTRIM (left) and RTRIM (right) functions that were introduced in the world of MS SQL server much earlier.

There was also a possibility to use a custom CRL function (C# String.Trim()) but this approach isn’t probably the best and the quickest one.

Example:

-- input value
DECLARE @StringValue nvarchar(max) = '  remove spaces from both sides of this string  ';
-- string with spaces
SELECT @StringValue AS Result UNION ALL
-- old way before introducing TRIM
SELECT LTRIM(RTRIM(@StringValue)) AS Result UNION ALL
-- new way with TRIM
SELECT TRIM(@StringValue) AS Result;

[characters FROM ] is optional. If not specified TRIM will remove space characters on both side of the specified string value. If a value (characters) is specified it will remove specified characters from both sides of the specified string value.

-- remove chars from both sides of the string
SELECT TRIM('STR' FROM 'STRmydataSTR') AS trimmed_string;
-- only the S will be removed since there is no R after/before the S character
SELECT TRIM('SR' FROM 'STRmydataSTR') AS trimmed_string;

MS SQL 2022 improved the TRIM function with additional options.

Syntax:

TRIM ([LEADING|TRAILING|BOTH] [characters FROM ]string)

  • String- An expression of any character type (nvarchar, varchar, nchar, or char) where characters should be removed.
  • Characters - A literal, variable, or function call of any non-LOB character type (nvarchar, varchar, nchar, or char) containing characters that should be removed. nvarchar(max) and varchar(max) types aren't allowed.
  • [LEADING|TRAILING|BOTH] - The optional first argument specifies which side of the string to trim:
    LEADING removes characters specified from the start of a string. With optional LEADING positional argument, the behavior is equivalent to LTRIM(@string, characters).
    TRAILING removes characters specified from the end of a string. With optional TRAILING positional argument, the behavior is equivalent to RTRIM(@string, characters).
    BOTH (default positional behavior) removes characters specified from the start and end of a string. Same as before without specifying BOTH.
-- New LEADING and TRAILING
SELECT TRIM(LEADING 'STR' FROM 'STRmydataSTR') AS leading_string;
SELECT TRIM(TRAILING 'STR' FROM 'STRmydataSTR') AS trailing_string;
-- Same as the previous release behavior but explicitly specifying BOTH
SELECT TRIM('STR' FROM 'STR mydata STR') as trim_strings;
SELECT TRIM(BOTH 'STR' FROM 'STRmydataSTR') as both_strings_trimmed;

LTRIM & RTRIM

LTRIM & RTRIM are functions that remove space characters from the beginning/end of the given string value.

In SQL 2022 the functions are enhanced with an additional optional parameter to define characters to be removed from the beginning/end of the given string value.

Syntax:

LTRIM (character_expression[, characters])

RTRIM (character_expression[, characters])

  • character_expression - An expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type, except text, ntext, and image, that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.
  • characters - A literal, variable, or function call of any non-LOB character type (nvarchar, varchar, nchar, or char) containing characters that should be removed. nvarchar(max) and varchar(max) types aren't allowed.

Let's see them in action:

-- The new extension to the LTRIM function
SELECT LTRIM('STRmydataSTR', 'STR') as left_trimmed_string;
-- The new extension to the RTRIM function
SELECT RTRIM('STRmydataSTR', 'STR') as right_trimmed_string;

*You can download the complete SQL Script with all examples from the post here: TRIM LTRIM RTRIM.sql (1.26 kb)