TRIM
Purpose
Takes a character or byte string_expression argument, trims the specified pad characters or bytes, and returns the trimmed string.
Syntax
where:
Syntax Element … |
Specifies … |
|||
BOTH |
how to trim the specified trim character or byte from string_expression. If you omit this option, the default is BOTH, and the default trim character is a null byte for byte types and a pad character for character types. |
|||
trim_expression |
the specific character or byte to trim from the head, tail, or both, of string_expression. The expression must evaluate to a single character. You cannot specify trim_expression without also specifying BOTH, TRAILING, or LEADING. You cannot specify a trim_expression of type KANJI1, nor can you apply a trim_expression to a string_expression of type KANJI1. |
|||
FROM |
a keyword required when BOTH, TRAILING, or LEADING are specified. |
|||
character_set |
the name of the server character set to associate with the string expression. |
|||
character_set
|
Valid values are: _Latin, which is the LATIN server character set _Unicode, which is the UNICODE server character set _KanjiSJIS, which is the KANJISJIS server character set _Graphic, which is the GRAPHIC server character set |
|||
string_expression |
a byte or character string or string expression to be trimmed. |
ANSI Compliance
This is ANSI SQL:2011 compliant.
Argument Types and Rules
The trim_expression argument must evaluate to a single byte that has a byte data type or single character that has a character data type.
TRIM operates on the following types of string_expression arguments:
If a numeric expression is used as the string_expression argument, it is converted implicitly to CHARACTER type.
To define an implicit cast for a UDT, use the CREATE CAST statement and specify the AS ASSIGNMENT clause. For more information on CREATE CAST, see SQL Data Definition Language.
Implicit type conversion of UDTs for system operators and functions, including TRIM, is a Teradata extension to the ANSI SQL standard. To disable this extension, set the DisableUDTImplCastForSysFuncOp field of the DBS Control Record to TRUE. For details, see Utilities: Volume 1 (A-K).
For more information on implicit type conversion, see Chapter 13: “Data Type Conversions.”
Result Type and Attributes
Here are the default result type and attributes for TRIM(string_expression):
It is possible for the length of the result to be zero.
The server character set of the result is the same as the argument.
If the string_expression argument is null, the result is null.
Concatenation With TRIM
The TRIM function is typically used with the concatenation operator to remove trailing pad characters or trailing bytes containing binary 00 from the concatenated string.
If the TRIM function is specified for character data types, leading, trailing, or leading and trailing pad characters are suppressed in the concatenated string, according to which syntax is used.
Example
If the Names table includes the columns first_name and last_name, which contain the following information:
first_name (CHAR(12)) has a value of 'Mary '
last_name (CHAR(12)) has a value of 'Jones '
then this statement:
SELECT TRIM (BOTH FROM last_name) || ', ' || TRIM(BOTH FROM first_name)
FROM names ;
returns the following string (note that the seven trailing blanks at the end of string Jones, and the eight trailing blanks at the end of string Mary are not included in the result):
'Jones, Mary'
If the TRIM function is removed, the statement:
SELECT last_name || ', ' || first_name
FROM names;
returns trailing blanks in the string:
'Jones , Mary '
Example
Assume column a is BYTE(4) and column b is VARBYTE(10).
If these columns contained the following values:
a b
------------ ---------
78790000 43440000
68690000 3200
12550000 332200
then this function:
SELECT TRIM (TRAILING FROM a) || TRIM (TRAILING FROM b) FROM ...
returns:
78794344
686932
12553322
Example
The following statement trims trailing SEMICOLON characters from the specified string.
SELECT TRIM( TRAILING ';' FROM textfield) FROM texttable;
Example
The following table illustrates several more complicated TRIM functions:
Function |
Result |
SELECT TRIM(LEADING 'a' FROM 'aaabcd'); |
'bcd' |
CREATE TABLE t2 INSERT t2 (1, 'aaabcd', 'a'); SELECT TRIM(LEADING c2 FROM c1) FROM t2; |
'bcd' |
CREATE TABLE t3 INSERT t3 (1, _Unicode '006100610061006200630064'XC); SELECT TRIM(LEADING _Unicode '0061'XC FROM t3.c1); |
'bcd' |
SELECT TRIM(_Unicode 'ΔΔabc |
'abc |
SELECT TRIM(_Unicode 'ΔΔabc |
'abc Δ (GRAPHIC pad) is not removed. |
CREATE TABLE t1 INSERT t1 (_Graphic 'abc SELECT TRIM(c1) from t1; |
'abc Δ (GRAPHIC pad) is removed because the operand of the TRIM function is of type GRAPHIC. |