15.00 - TRIM - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

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.