Trim - Teradata Warehouse Miner

Teradata® Warehouse Miner™ User Guide - Volume 2ADS Generation

Product
Teradata Warehouse Miner
Release Number
5.4.6
Published
November 2018
Language
English (United States)
Last Update
2018-12-07
dita:mapPath
gxn1538171534877.ditamap
dita:ditavalPath
ft:empty
dita:id
B035-2301
Product Category
Software

The semi-standard Trim function is supported for removing leading and/or trailing pad characters or bytes or a specified single character from a character string. The ability to specify a character set for the expression is, however, not supported.

Some examples of pad characters include spaces, tabs or binary 00s for character data, leading 0s or plus sign for numeric data, or binary 00s for byte data. The SQL generated may take one of the following forms.

TRIM(expression)
TRIM(LEADING/TRAILING/BOTH FROM expression)
TRIM(LEADING/TRAILING/BOTH char FROM expression)

The expression to trim may be of a character, numeric, date or byte type, with a numeric or date expression being automatically converted to a character expression before trimming. The type returned is VARCHAR (or VARBYTE for byte data). When dragging a Trim operator into a variable, the following tree element is created.

Variable Creation > Input > Variables: SQL Elements pane - String Functions > Trim

Columns and/or other expressions can be moved into the (empty) branch of the tree. The value to trim and the type of trimming are specified in the Properties panel. Double-click on Trim, or highlight it and click Properties.

Variable Creation > Input > Variables: SQL Elements pane - String Functions > Trim Properties

Valid Trim Styles are (Default), Leading, Trailing or Both. If (Default) is specified, both leading and trailing pad characters (or null bytes for byte type data) are trimmed. Any type of character can be specified to be trimmed in Value to Trim.

The value to trim will automatically be enclosed in quotes when SQL is generated for the value. If a single quote mark is included in the value, it will automatically be “escaped” by doubling it. If, however, more than one quote mark is entered, the value is placed in the SQL “as-is”, without adding quote marks. This makes it possible to enter a hexadecimal literal if desired, such as '00'XC.