askvity

How to trim string in SQL?

Published in SQL String Functions 4 mins read

To trim a string in SQL, you typically use functions like TRIM(), LTRIM(), or RTRIM() to remove leading or trailing spaces or specified characters.

String Trimming Functions in SQL

SQL provides standard functions to remove unwanted characters, most commonly spaces, from the beginning or end of strings. The primary functions are TRIM(), LTRIM(), and RTRIM().

The TRIM() Function

The TRIM() function is versatile and part of the SQL standard. It removes the space character OR other specified characters from the start or end of a string.

  • Default Behavior: By default, the TRIM() function removes leading and trailing spaces from a string.
  • Custom Characters: You can also specify a character or set of characters to remove from either the leading, trailing, or both sides of the string.

Syntax:

TRIM([LEADING | TRAILING | BOTH] [characters FROM] string)
  • LEADING: Removes characters from the beginning of the string.
  • TRAILING: Removes characters from the end of the string.
  • BOTH: Removes characters from both the beginning and the end (this is the default if none are specified).
  • characters: The character(s) to remove. Defaults to space if not specified.
  • string: The input string.

Examples:

  • Remove default spaces from both ends:

    SELECT TRIM('   Hello World!   ');
-- Result: 'Hello World!'

  • Remove specific character from both ends:

    SELECT TRIM('X' FROM 'XXXHello World!XXX');
-- Result: 'Hello World!'

  • Remove specific characters from the leading end:

    SELECT TRIM(LEADING 'abc' FROM 'abcbaHello World!ab');
-- Result: 'Hello World!ab'

    (Note: TRIM removes any combination of the specified characters from the defined end)

  • Remove specific characters from the trailing end:

    SELECT TRIM(TRAILING 'X!' FROM 'Hello World!XXX!!');
-- Result: 'Hello World'

The LTRIM() Function

The LTRIM() function specifically removes characters from the leading (left) side of a string. In most SQL dialects (including SQL Server as mentioned in the reference), it defaults to removing leading spaces. Some dialects allow specifying other characters.

Syntax (SQL Server):

LTRIM(string)

Syntax (Other Dialects, e.g., PostgreSQL, MySQL):

LTRIM(string, [characters])

Examples (SQL Server - removes leading spaces):

SELECT LTRIM('   Hello World!');
-- Result: 'Hello World!   '

The RTRIM() Function

The RTRIM() function specifically removes characters from the trailing (right) side of a string. Similar to LTRIM(), it typically defaults to removing trailing spaces.

Syntax (SQL Server):

RTRIM(string)

Syntax (Other Dialects, e.g., PostgreSQL, MySQL):

RTRIM(string, [characters])

Examples (SQL Server - removes trailing spaces):

SELECT RTRIM('Hello World!   ');
-- Result: 'Hello World!'

Comparing TRIM, LTRIM, and RTRIM

Here's a quick comparison of the standard behaviors (removing spaces):

Function Removes From Can Remove Specified Characters
TRIM() Both ends Yes
LTRIM() Leading end Yes (in some dialects)
RTRIM() Trailing end Yes (in some dialects)

In SQL Server, as hinted by the reference mentioning LTRIM() and RTRIM() alongside TRIM(), LTRIM() and RTRIM() primarily remove only leading/trailing spaces, while TRIM() provides the ability to remove specified characters from either end or both.

Choosing the right function depends on whether you need to remove characters from the left, right, or both sides, and whether you are removing default spaces or specific characters. For simple space removal, LTRIM and RTRIM are straightforward. For more control, especially removing non-space characters, TRIM is the preferred function.

Related Articles