15.00 - TRIM - Teradata Database

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 TRAILING LEADING	how to trim the specified trim character or byte from string_expression. BOTH means trim both trailing and leading characters or bytes. TRAILING means trim only trailing characters or bytes. LEADING mens trim only leading characters or bytes. 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 (continued)	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:

Character, except for CLOB

Byte, except for BLOB

Numeric

If a numeric expression is used as the string_expression argument, it is converted implicitly to CHARACTER type.

UDTs that have implicit casts to any of the following predefined types:

Character

Numeric

Byte

DATE

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):

If string_expression is a byte string, the result type is VARBYTE.

If the string_expression is a numeric expression or character string, the result type is VARCHAR.

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   (i1 INTEGER, c1 CHAR(6), c2 CHAR(1)); INSERT t2 (1, 'aaabcd', 'a'); SELECT TRIM(LEADING c2 FROM c1) FROM t2;	'bcd'
CREATE TABLE t3   (i1 INTEGER, c1 CHAR(6) CHAR SET UNICODE); 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   (c1 CHARACTER(6) CHARACTER SET GRAPHIC); 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.