TRIM
TRIM combines the 
BTRIM, LTRIM, and RTRIM functions into a single function.
RTRIM returns a VARCHAR value representing a string with specific characters having been removed from the right side (end).
Syntax
RTRIM ( expression [ , characters ] )
Semantics
expression
|
(CHAR or VARCHAR) is the string to trim
|
characters
|
(CHAR or VARCHAR) specifies the characters to remove from the right side of expression. The default is the space character.
|
Examples
> SELECT RTRIM('trimzzzyyyyyyxxxxxxxx', 'xyz');
ltrim
-------
trim
(1 row)
LTRIM returns a VARCHAR value representing a string with specific characters having been removed from the left side (beginning).
Syntax
LTRIM ( expression [ , characters ] )
Semantics
expression
|
(CHAR or VARCHAR) is the string to trim
|
characters
|
(CHAR or VARCHAR) specifies the characters to remove from the left side of expression. The default is the space character.
|
Examples
> SELECT LTRIM('zzzyyyyyyxxxxxxxxtrim', 'xyz');
ltrim
-------
trim
(1 row)
BTRIM removes the longest string consisting only of specified characters from the start and end of a string.
Syntax
BTRIM ( expression [ , characters-to-remove ] )
Semantics
expression
|
(CHAR or VARCHAR) is the string to modify
|
characters-to-remove
|
(CHAR or VARCHAR) specifies the characters to remove. The default is the space character.
|
Examples
BTRIM('xyxtrimyyx', 'xy')
Result: trim
Syntax
TRIM ( [ [ LEADING | TRAILING | BOTH ] characters FROM ] expression )
Semantics
LEADING
|
removes the specified characters from the left side of the string
|
TRAILING
|
removes the specified characters from the right side of the string
|
BOTH
|
removes the specified characters from both sides of the string (default)
|
characters
|
(CHAR or VARCHAR) specifies the characters to remove from expression. The default is the space character.
|
expression
|
(CHAR or VARCHAR) is the string to trim
|
Examples
Retail_Schema=> SELECT '-' || TRIM(LEADING 'x' FROM 'xxdatabasexx') || '-';
?column?
--------------
-databasexx-
(1 row)
Retail_Schema=> SELECT '-' || TRIM(TRAILING 'x' FROM 'xxdatabasexx') || '-';
?column?
--------------
-xxdatabase-
(1 row)
Retail_Schema=> SELECT '-' || TRIM(BOTH 'x' FROM 'xxdatabasexx') || '-';
?column?
------------
-database-
(1 row)
Retail_Schema=> SELECT '-' || TRIM('x' FROM 'xxdatabasexx') || '-';
?column?
------------
-database-
(1 row)
Retail_Schema=> SELECT '-' || TRIM(LEADING FROM ' database ') || '-';
?column?
--------------
-database -
(1 row)
Retail_Schema=> SELECT '-' || TRIM(' database ') || '-';
?column?
------------
-database-
(1 row)
