1. SQL Reference Guide
  2. Built-in Functions
  3. Functions on Strings
  4. trim Function

trim Function

The trim function enables you to trim leading or trailing characters (or both) from a string.

Syntax

returnvalue trim(source [, position [, trim_character]])

source ::= any*
position ::= "leading"|"trailing"|"both"
trim_character ::= string*
returnvalue ::= string

Semantics

source

The input string that should be trimmed. This argument is implicitly cast to a sequence of strings.

If you provide only the source argument, then the leading and trailing blank spaces are removed.

position
This argument indicates whether leading or trailing or both leading and trailing characters should be removed. The following are the valid values that can be specified for this argument.
  • If leading is specified, then the characters equal to the trim_character argument are removed from the beginning of the string.
  • If trailing is specified, then the characters equal to the trim_character argument are removed at the end of the string.
  • If both is specified, then the characters equal to the trim_character argument are removed from both the beginning and end of the string.
  • If no value is specified, then both value is assumed.
  • If any value other than the above valid values are specified, then NULL is returned.
trim_character

This argument specifies the characters that should be removed from the source string. If you do not specify this argument, then a blank space is taken as the default value.

Only one character is allowed for this argument. If there are more than one character, then the first character will be used.

If an empty string is specified, then no trimming happens.

return_value

Returns NULL if any of the arguments is NULL.

Returns NULL if any argument is an empty sequence or a sequence with more than one item.

Example 12-9 trim function

Create this table and insert values in it to run the trim, ltrim, and rtrim function examples.

CREATE TABLE trim_demo (
  id INTEGER,
  name STRING,
  yearofbirth STRING,
  PRIMARY KEY (id)
);

INSERT INTO trim_demo VALUES (10, "  Peter  ", 1980);
INSERT INTO trim_demo VALUES (20, "Mary", 1973);
INSERT INTO trim_demo VALUES (30, "  Oliver", 2000);
INSERT INTO trim_demo VALUES (40, "John  ", 2000);

SELECT * FROM trim_demo;
 +----+-----------+-------------+
 | id |   name    | yearofbirth |
 +----+-----------+-------------+
 | 10 |   Peter   | 1980        |
 | 20 | Mary      | 1973        |
 | 30 |   Oliver  | 2000        |
 | 40 | John      | 2000        |
 +----+-----------+-------------+

Example 12-10 trim Function

In this example, the id and yearofbirth are selected from the trim_demo table. Notice that the zeros at the end of the yearofbirth are removed using the trim function. 

SELECT id, trim(yearofbirth,"trailing",'0') FROM trim_demo;
 +----+----------+
 | id | Column_2 |
 +----+----------+
 | 10 | 198      |
 | 20 | 1973     |
 | 30 | 2        |
 | 40 | 2        |
 +----+----------+

Example 12-11 trim Function

In this example, '19' is provided as the trim_character. However, as per semantics, only the first character '1' will be considered for trimming.

SELECT id, trim(yearofbirth,"leading",'19') FROM trim_demo;
 +----+----------+
 | id | Column_2 |
 +----+----------+
 | 10 | 980      |
 | 20 | 973      |
 | 30 | 2000     |
 | 40 | 2000     |
 +----+----------+