TRIM

The TRIM function in SQL is used to remove leading, trailing, or both leading and trailing unwanted characters (often whitespace) from a string.

Syntax

TRIM([LEADING | TRAILING | BOTH] [removal_string] FROM target_string)

If LEADING, TRAILING, or BOTH is not specified, TRIM function will remove both leading and trailing spaces.

Examples

Here we are creating a table named Item with a default value for the name column. The default value is obtained by concatenating two strings. The first string is the result of trimming leading 'a' from 'aabc' and the second string is the result of trimming spaces from ' good '.

The TRIM function is used in a SELECT statement to remove leading and trailing spaces from the name column in the Item table:

SELECT TRIM(name) FROM Item;

The TRIM function can also be used with NULL values. If the value is NULL, the TRIM function will return NULL.

SELECT TRIM(name) AS test FROM NullName;

You can also specify a specific character to remove from the string. The following example removes 'xyz' from the string:

SELECT TRIM(BOTH 'xyz' FROM name) FROM Test;

The LEADING and TRAILING keywords can be used to remove characters from the beginning or the end of the string, respectively:

SELECT TRIM(LEADING 'xyz' FROM name) FROM Test;
SELECT TRIM(TRAILING 'xyz' FROM name) FROM Test;

You can also nest TRIM functions:

SELECT TRIM(BOTH TRIM(BOTH ' potato ')) AS Case1;

The TRIM function requires string values. If you try to use it with a non-string value, an error will occur:

SELECT TRIM('1' FROM 1) AS test FROM Test;