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.
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| Argument | Meaning |
|---|---|
| String | The character or byte expression to extract from. |
| Start | The position of the first character to return, starting at 1. |
| Length | How 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;
| Column | Expected value |
|---|---|
| last_four | data |
| remaining_text | data |
| middle_text | era |
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
| Case | Behavior |
|---|---|
| Start beyond the end | Returns an empty string for a nonnegative length. |
| Length is zero | Returns an empty string. |
| Length exceeds the remaining text | Returns the available characters through the end. |
| Length is negative | Returns an error. Validate a calculated length before calling the function. |
| Input string is NULL | Returns 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.
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