16.20 - SEPARATOR - Basic Teradata Query

Basic Teradata Query Reference

Basic Teradata Query
October 2018
Programming Reference


This control enables users to specify the width and contents of the string to be placed between the columns in a report.


where the following is true:

The number of spaces that separate columns.

The system value is 2 by default. The maximum value is 254.

The string that separates columns.

The system value is 2 spaces by default.

Usage Notes

The .SET SEPARATOR n form of the SEPARATOR command inserts n spaces between columns. The .SET SEPARATOR ‘string’ form of the command inserts the specified string between columns.

If a separator value prevents a report from fitting on a page, BTEQ truncates the separator on the right until the report fits on the page. BTEQ never truncates the separator to less than one character.

Do not use special characters within the string as they might be interpreted differently by different output devices. A script that uses special characters might required modification if output is routed to another device.

If the string has an apostrophe (single quote) character, use the second form of the SEPARATOR command (the one with quotes as delimiters) or double the apostrophe. For example:

.SET SEPARATOR "  Item's value "


.SET SEPARATOR '  Item''s value ' 

If the string does not contain an apostrophe, the two forms of the SEPARATOR command are the same.

If a value for n is not specified, then n = 0.

The optional ALL parameter enables the separators to perform the following tasks:

  • Appear on ALL data lines including the summary lines associated with WITH clauses.
  • Omit the column title lines if they contain only spaces and separators.

Do not use the .SET SEPARATOR ALL command with the SIDETITLES or FOLDLINE commands. Also, when the ALL parameter is used with the summary titles appearing to the left side of the data, the summary titles might be over-written by the separators.

The SEPARATOR setting only affects the output for Field Mode, not Record Mode, Indicator Mode, or Multipart Indicator Mode.

The SEPARATOR command is valid in a Teradata SQL macro.

Example 1 – SEPARATOR

To specify a vertical bar as the column separator character, type:

 database Workforce;
.format on
.heading "set separator |"
.set separator "|"
select Name
from Employee
order by Name
.format off

BTEQ Response

*** Query completed. 21 rows found. 3 columns returned.  
set separator |                      
Name          |EmpNo|JobTitle
------------   ----- ------------
Aguilar J     |10007|Manager
Brangle B     |10020|Salesperson
Carter J      |10016|Engineer
Chin M        |10011|Controller
Clements D    |10022|Salesperson
Greene W      |10017|Payroll Ck
Inglis C      |10014|Tech Writer
Kemper R      |10006|Assembler
Leidner P     |10003|Secretary
Marston A     |10009|Secretary
Moffit H      |10002|Recruiter

Example 2 – SEPARATOR

To specify four spaces as the column separator, type the following:


Example 3 – SEPARATOR instigated by a macro

The prior example’s SEPARATOR command in a Teradata SQL macro appears as follows:


Example 4 – SEPARATOR

To specify the ALL parameter, type the following:

set separator '|' all