Use TO_NUMBER to parse numeric text, TRUNC to discard decimal digits, and ROUND to round them. FLOOR and CEILING move toward negative and positive infinity respectively. Those differences matter for negative values. A letter-case comparison is not a numeric validator.
Lab check: 24 October 2026 · Teradata 20.00.28.81 · BTEQ 20.00.00.11 · BTET. The values below were evaluated in the lab; decimal display padding can vary by client. Download the SQL and observed results.
Parse numbers without confusing invalid input and missing data
SELECT TO_NUMBER('12.77') AS parsed_number,
CASE WHEN TO_NUMBER('not-a-number') IS NULL THEN 1 ELSE 0 END AS invalid_is_null,
CASE WHEN TO_NUMBER(CAST(NULL AS VARCHAR(12))) IS NULL THEN 1 ELSE 0 END AS source_null;Observed: 12.77, 1, 1. Conversion failure and an already-NULL input both produced NULL. Check the original input separately if your pipeline must distinguish missing values from malformed text. Teradata documents that invalid format arguments can raise an error; a NULL conversion result is not a guarantee that every call is error-free.
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 bookThe older UPPER(value) = LOWER(value) shortcut was removed: punctuation can satisfy it without representing a number. Choose an explicit format when parsing locale-specific separators, and test it against representative input.
TRUNC, FLOOR, CEILING and ROUND: tested negative-value examples
SELECT TRUNC(CAST(-12.67 AS DECIMAL(8,2))) AS truncated,
FLOOR(CAST(-12.67 AS DECIMAL(8,2))) AS floored,
CEILING(CAST(-12.67 AS DECIMAL(8,2))) AS ceiling_value,
ROUND(CAST(12.1266 AS DECIMAL(10,4)),2) AS rounded;| Expression | Observed numeric result |
|---|---|
| TRUNC(-12.67) | -12 |
| FLOOR(-12.67) | -13 |
| CEILING(-12.67) | -12 |
| ROUND(12.1266,2) | 12.13 |
LEAST and GREATEST
LEAST(12.50, 1.00, 2.88) selects the minimum, 1.00; GREATEST(12.50, 1.00, 2.88) selects the maximum, 12.50. These illustrative expressions are separate from the executed test set above. Check NULL handling and compatible argument types when adapting them.
Common numeric-function mistakes
- Using FLOOR as a synonym for TRUNC on negative numbers.
- Treating a NULL conversion as proof that the source was malformed rather than absent.
- Relying on implicit character-to-number conversion without defining separators and precision.
- Assuming display formatting changes the stored numeric value.
Reference: Teradata TO_NUMBER syntax, conversion behavior and format rules. Continue with Teradata data types and date formatting and conversion.
How should a load distinguish invalid numeric text from NULL?
Retain the original value and classify source NULL separately from a non-NULL value whose conversion returns NULL. Preserve rejected text for an authorized data-quality review. Decide explicitly how to handle blank strings, thousands separators and decimal separators; one successful conversion does not validate every locale.
Why do FLOOR and TRUNC disagree on negative values?
FLOOR moves toward negative infinity, whereas TRUNC removes fractional digits toward zero. The tested -12.67 example therefore produces -13 with FLOOR and -12 with TRUNC. Choose based on the business rule rather than treating the functions as interchangeable.
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