TRIMTRAILINGSPACES - Basic Teradata Query - 16.20

Basic Teradata Query Reference

prodname
Basic Teradata Query
vrm_release
16.20
category
Programming Reference
featnum
B035-2414-108K

Purpose

This control enables users to specify whether to trim trailing spaces from Field Mode response lines.

Syntax



Usage Notes

Use the TRIMTRAILINGSPACES command to control trimming of trailing spaces from Field Mode response lines.

By default, TRIMTRAILINGSPACES is ON, which means BTEQ will trim all trailing spaces from Field Mode response lines. Use the OFF setting to inhibit BTEQ from trimming the trailing spaces.

The TRIMTRAILINGSPACES setting only affects the output for Field Mode, not Record Mode, Indicator Mode, or Multipart Indicator Mode. In other words, TRIMTRAILINGSPACES setting affects only those response lines that go to stdout or EXPORT REPORT files, and not those that go to EXPORT DATA, EXPORT INDICDATA or EXPORT DIF files.

Note that this setting affects only the columnar response lines. For example, it affects the SELECT or HELP query output but does not affect the SHOW or COMMENT query output.

Trimming the trailing spaces from Field Mode response lines could be beneficial for stdout files as it minimizes the number of bytes used for tabular report output. However, for exported reports, trimming of trailing spaces can cause issues for post-processing that expects fixed-size records.

The SET DEFAULTS command will reset TRIMTRAILINGSPACES setting to ON.

The TRIMTRAILINGSPACES command is valid in a Teradata SQL macro.

Example 1 – TRIMTRAILINGSPACES ON

The following example selects two columns using TRIMTRAILINGSPACES set to ON (default).

.SET TRIMTRAILINGSPACES ON /* Default setting */
SELECT 'aaaa' (title 'Column-1'), 'bb' (title 'Column-2');

Output:

Column-1  Column-2
--------  --------
aaaa      bb      

Example 2 – TRIMTRAILINGSPACES OFF

The following example selects two columns using TRIMTRAILINGSPACES set to OFF.

.SET TRIMTRAILINGSPACES OFF
SELECT 'aaaa' (title 'Column-1'), 'bb' (title 'Column-2');

Output:

Notice that the trailing spaces are intact in the second column "bb      ".

Column-1  Column-2
--------  --------
aaaa      bb