SQL-J Language Reference
Page 85 of 121

TRIM

The TRIM function trims one or more instances of a single character from the beginning or end (or both) of a character expression. By default, TRIM trims one blank space from the beginning and one from the end of a character expression.

The type of the result is VARCHAR. The length of the result is the maximum length of the source type.

Syntax

TRIM( [ [ { LEADING | TRAILING | BOTH } ]
    [ trimCharacter ] FROM ] CharacterExpression )

A CharacterExpression is a CHAR, VARCHAR, or LONG VARCHAR data type, any built-in type that is implicitly converted to a string, or any Java data type that directly maps to a built-in type that is implicitly converted to a string.

BOTH is implicit if LEADING, TRAILING, or BOTH is not specified.

trimCharacter must be one single character. If trimCharacter is not specified, ' ' (blank) is implicit.

If TRAILING is specified, TRIM removes one or more trailing characters equal to trimCharacter from the result.

If LEADING is specified, TRIM removes one or more leading characters equal to trimCharacter from the result.

If BOTH is specified (or implied by its absence), TRIM removes one or more leading or trailing characters equal to the trimCharacter from the result.

TRIM(CharacterExpression) is the same as TRIM(BOTH ' ' FROM CharacterExpression).

If FROM is specified, you must specify either or both of the following:

  • LEADING, TRAILING, or BOTH
  • trimCharacter

If either trimCharacter or CharacterExpression is a user-specified null, the result of the expression is null.

TRIM Examples

-- returns 'asd f'
VALUES
TRIM(' asd f ')

-- returns 'asdf '
VALUES
TRIM('x' FROM 'xasdf x')

-- throws exception,
VALUES
TRIM(TRAILING 'as' FROM 'asdf')
-- trimCharacter is not a single character

-- returns 'asd'
VALUES
TRIM(TRAILING 'f' FROM 'asdf')

-- returns 'babaac'
VALUES
TRIM ('a' FROM 'aababaacaa')