DWHPro Insights

Teradata SUBSTRING and SUBSTR: Syntax and Examples

By Roland Wenzlofsky · Published · Updated
Tested on Teradata 20.00.28.81 · ASCII string examples

Teradata SUBSTRING and SUBSTR: Syntax and Examples

Teradata SUBSTRING extracts part of a string using a starting position and a length. The first character is position 1. Use SUBSTRING(value FROM start FOR length) for ANSI syntax, or SUBSTR(value, start, length) for the Teradata form. The third argument is a character count, not an end position.

Lab check: 24 October 2026 · Teradata 20.00.28.81 · BTEQ 20.00.00.11 · BTET. These ASCII string examples were executed as separate requests; other character sets and ANSI-session result widths were not tested. Download the SQL and observed results.

Verified SUBSTRING results and boundary cases

SELECT SUBSTRING('Teradata' FROM 1 FOR 4) AS ansi_text,
       SUBSTR('Teradata', 2, 3) AS middle_text,
       CHARACTER_LENGTH(SUBSTRING('ABC' FROM 8 FOR 2)) AS empty_length,
       SUBSTRING('ABC' FROM 2 FOR 10) AS remaining_text;

Observed result: Tera, era, 0 and BC. A separate NULL-input check preserved NULL. The guarded delimiter example returned NULL for AT0042, which has no hyphen.

A start position beyond the string produced an empty string, not NULL. Keep those cases separate when validating imported data. The length argument is a count, not the position of the last character.

SUBSTRING versus SUBSTR: syntax

-- ANSI form
SELECT SUBSTRING('Teradata' FROM 1 FOR 4) AS part;

-- Teradata form
SELECT SUBSTR('Teradata', 1, 4) AS part;

Both examples are expected to return Tera. Teradata also accepts the comma-separated form with the name SUBSTRING. Prefer the ANSI form for new code when it fits your portability requirements; test behavior on the destination database when migrating.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book
ArgumentMeaning
StringThe character or byte expression to extract from.
StartThe position of the first character to return, starting at 1.
LengthHow many characters to return. Omit it to read from the start position to the end.

Examples with expected results

SELECT
    SUBSTRING('Teradata' FROM 5 FOR 4) AS last_four,
    SUBSTRING('Teradata' FROM 5) AS remaining_text,
    SUBSTR('Teradata', 2, 3) AS middle_text;
ColumnExpected value
last_fourdata
remaining_textdata
middle_textera

Extract a fixed-position field

For a value such as AT-2026-0042, positions 4 through 7 contain the year. Four characters are required:

SELECT SUBSTRING('AT-2026-0042' FROM 4 FOR 4) AS year_text;

Expected result: 2026 as text. Use this pattern only when the source format is stable. For actual DATE values, use date functions such as EXTRACT instead of depending on a displayed date string.

Extract text after a delimiter

Find the delimiter first and define what should happen when it is absent. This example returns NULL when there is no hyphen:

SELECT
    CASE
        WHEN POSITION('-' IN 'AT-0042') > 0
        THEN SUBSTRING('AT-0042'
                       FROM POSITION('-' IN 'AT-0042') + 1)
        ELSE NULL
    END AS suffix;

Expected result: 0042. For multiple delimiters or one output row per token, see the SQL learning path for the string-splitting guide.

Empty results, NULLs, and invalid lengths

CaseBehavior
Start beyond the endReturns an empty string for a nonnegative length.
Length is zeroReturns an empty string.
Length exceeds the remaining textReturns the available characters through the end.
Length is negativeReturns an error. Validate a calculated length before calling the function.
Input string is NULLReturns NULL; an empty string and NULL are different values.
SELECT
    CHARACTER_LENGTH(SUBSTRING('ABC' FROM 8 FOR 2)) AS empty_length,
    SUBSTRING('ABC' FROM 2 FOR 10) AS available_text;

Expected results: 0 and BC. Use positive starting positions in application code. Do not assume a negative position counts backward from the end as it does in some other SQL dialects; consult the release-specific rules for nonpositive positions.

Data types and trailing spaces

Character input normally returns VARCHAR, byte input returns VARBYTE, and large-object input preserves the CLOB or BLOB type. Numeric input can be implicitly converted to character data, but an explicit conversion makes the intended representation clearer. If you omit the length for CHAR or BYTE input, Teradata trims trailing pad characters or binary zeros.

The declared result width can differ between ANSI and Teradata session modes. Check it with TYPE when building a table from an expression or defining a downstream interface; the number of returned characters and the declared width are separate things.

Performance: projection and filtering are different

Selecting only the characters you need can reduce the amount of data carried forward. It does not guarantee less spool or a faster query: result types, the execution plan, and the rest of the workload matter.

A function applied to a column in a WHERE or JOIN condition can change the available access paths. Compare EXPLAIN plans and measured workload results before replacing a predicate. For prefix matching, evaluate a suitable LIKE predicate where it preserves the required semantics.

Official reference

Teradata SUBSTRING reference: syntax, result types, and usage rules. Examples above illustrate the documented behavior; confirm them against your installed release and session settings.

Why does a substring extraction return the wrong field?

Check the starting position, the requested character count and whether the delimiter exists. Do not pass an end position as the length. If the input is a DATE or numeric value, convert it explicitly before applying string-position rules; otherwise the displayed representation can vary.

Why does a delimiter-free row return the whole string?

POSITION returns zero when the delimiter is missing. Adding one yields position 1, so an unguarded extraction starts at the beginning. The CASE guard in this article deliberately returns NULL instead. Choose and document whether missing delimiters should produce NULL, the original value or a rejected record.

Deciding what to change in your data platform?

Compare optimisation, modernisation and migration against your workload and business requirements.

Explore independent platform assessments

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam