TRIM function to remove leading and trailing characters from a string - MaxCompute

Removes leading and trailing characters from a string.

Usage notes

The TRIM function supports only English characters.

Syntax

-- Function syntax
string trim(string <str>[, <trimChars>])

-- Standard SQL syntax
string trim([BOTH] [<trimChars>] from <str>)

Parameters

Parameter	Required	Type	Description
`str`	Yes	STRING	The string to trim. If the value is BIGINT, DECIMAL, DOUBLE, or DATETIME, it is implicitly converted to STRING before processing.
`trimChars`	No	STRING	The characters to remove. Each character in `trimChars` is treated individually. The function removes the longest substring from both ends that consists only of characters in this set. If omitted, spaces are removed.

Return value

Returns a STRING value.

If str is not STRING, BIGINT, DOUBLE, DECIMAL, or DATETIME, an error is returned.
If str or trimChars is null, null is returned.

Examples

A. Remove leading and trailing spaces

-- Returns yxTxyomxx
select trim(' yxTxyomxx ');

-- Equivalent forms
select trim(both from ' yxTxyomxx ');
select trim(from ' yxTxyomxx ');

B. Remove a custom set of characters

Remove any combination of x and y from both ends of the string yxTxyomxx:

-- Returns Txyom
select trim('yxTxyomxx', 'xy');

-- Equivalent forms
select trim(both 'xy' from 'yxTxyomxx');
select trim('xy' from 'yxTxyomxx');

The function strips characters from the ends inward. In yxTxyomxx, the leading y and x are removed because they belong to the character set xy. The trailing xx is also removed for the same reason. Stripping stops at T because it is not in the set, preserving the inner substring Txyom.

C. Null input

-- Both return null
select trim(null);
select trim('yxTxyomxx', null);

Related functions

LTRIM: Removes leading characters only.
RTRIM: Removes trailing characters only.
String functions: Full list of string functions in MaxCompute.