Introduction
I recently started working with Teradata for a client, and my initial line of learning has been to compare and constrast Teradata functionality with Oracle, the database I know best. This post covers Teradata numeric functions and their Oracle equivalents. I’ve included their SQL Server and MySQL equivalents as well.
Teradata Numeric Functions
Here’s a list of Teradata numeric functions, though note that two of these (exponentiation and modulus) are actually operators.
 Exponentiation operator (
**
)  Modulus operator (
MOD
) ABS
functionCASE_N
functionEXP
functionLN
functionLOG
functionNULLIFZERO
functionRANDOM
functionRANGE_N
functionSQRT
functionWIDTH_BUCKET
functionZEROIFNULL
function
Exponentiation Operator
Teradata uses the **
operator for exponentiation, just like many nondatabase programming languages. This is not an ANSI standard operator.
Oracle, SQL Server, and MySQL use the POWER
function for exponentiation. MySQL also supports the shorternamed POW
function.
Database  Expression  Result 
Teradata  2 ** 5 
32 
Oracle  POWER(2, 5) 
32 
SQL Server  POWER(2, 5) 
32 
MySQL  POWER(2, 5) 
32 
POW(2, 5) 
32 
Modulus Operator
Teradata uses the MOD
operator for the modulus, which (for positive numbers at least) is the remainder when a number is divided by another number. This is not an ANSI standard operator. Here’s how to get the modulus in Teradata, Oracle, SQL Server, and MySQL (the examples use 5 modulus 3, which asks for the remainder when 5 is divided by 3; the answer is 2):
Database  Expression  Result 
Teradata  5 MOD 3 
2 
Oracle  MOD(5, 3) 
2 
SQL Server  5 % 3 
2 
MySQL  MOD(5, 3) 
2 
5 % 3 
2 
ABS Function
The ABS
function returns a number’s absolute value. For zero and positive numbers, this is the number itself. For negative numbers it’s the number multiplied by 1, which turns it positive. This is not an ANSIstandard function.
Oracle, SQL Server, and MySQL all support this function, and their syntax is identical to Teradata’s:
ABS(32) → 32 ABS(79) → 79 ABS(0) → 0
CASE_N Function
The CASE_N
function takes a list of logical expressions and returns the index of the first expression that evaluates to true: if the first expression is true it returns 1, if the second expression is true it returns 2, if the third expression is true it returns 3, and so forth. It includes keywords to return a value if all of the expressions are false (NO CASE
), or if all of the expressions are indeterminate due to null values (UNKNOWN
), or both (NO CASE OR UNKNOWN
).
The CASE_N
function is targeted to partitioning, where rows in a table can be stored in different data files, often on different disks. With partitioning, the table definition assigns a partition based on table values. Partitions are numbered 1 through n, and the CASE_N
function is a convenient shorthand for assigning a value from 1 through n.
This function is not case sensitive, and there is no equivalent Oracle, SQL Server, or MySQL function. To get similar functionality in other databases, use the CASE
construct.
The Teradata documentation for this function is good but it lacks examples, something I find useful when figuring out a function. The examples here use this table:
SELECT * FROM test_casen; col1 col2 col3 case_example     null null null null 22 check 20101223 null 123 text 20130830 2
Here’s a CASE_N
query using its values:
SELECT col1, col2, col3, CASE_N( col1 = 122, col2 = 'text', col3 = date '20130830', NO CASE) AS case_ex FROM test_casen ORDER BY col1; col1 col2 col3 case_ex     null null null null 22 check 20101223 4 123 text 20130830 2
 For the first row, the conditions are indeterminate because of the null values so
CASE_N
can’t be evaluated. It returns null.  For the second row, the first three conditions evaluate to false. The fourth condition is
NO CASE
, which means “all the others are false” – think of it as like anelse
in anif/then/else
statement. Because the fourth argument evaluates to true, the function returns4
.  For the third row, the second condition (
col2 = 'text'
) evaluates to true, soCASE_N
returns 2.
In this query, the CASE_N
uses the UNKNOWN
keyword as the fifth argument. The UNKNOWN
matches the first row, where none of the other conditions evaluate to true because of the nulls:
SELECT col1, col2, col3, CASE_N( col1 = 122, col2 = 'text', col3 = date '20130830', NO CASE, UNKNOWN) AS case_example FROM test_casen ORDER BY col1; col1 col2 col3 case_example     null null null 5 22 check 20101223 4 123 text 20130830 2
Now the first row returns 5 because UNKNOWN
is the fifth condition in the CASE_N
.
To have CASE_N
return 4 when conditions 1, 2, and 3 aren’t true or are indeterminate due to the null values, use the NO CASE OR UNKNOWN
keyword for the fourth argument:
SELECT col1, col2, col3, CASE_N( col1 = 122, col2 = 'text', col3 = date '20130830', NO CASE OR UNKNOWN) AS case_example FROM test_casen ORDER BY col1; col1 col2 col3 case_example     null null null 4 22 check 20101223 4 123 text 20130830 2
EXP Function
The EXP
function raises the natural logarithm base e to an exponent value. Its parameter is the exponent, so for example EXP(3)
is e^{3}
. This is not an ANSI function.
Oracle, SQL Server, and MySQL also support the EXP
function. If you ever need the value of e, call EXP(1)
.
LN Function
The LN
function returns the natural log of a number. The natural log uses base e. This is not an ANSI standard function. Oracle and MySQL support the LN
function (MySQL also supports the synonym LOG
. In SQL Server, the function name is LOG
.
The value of e (approximately 2.718281828459045) is used in the examples below:
Database  Expression  Result 
Teradata  LN(2.718281828459045) 
1.0 
Oracle  LN(2.718281828459045) 
1 
SQL Server  LOG(2.718281828459045) 
1 
MySQL  LN(2.718281828459045) 
1 
LOG(2.718281828459045) 
1 
LOG Function
The LOG
function returns the base 10 log of a number. This is not an ANSI standard function. Oracle supports the LOG
function, but it requires the base (10) as the first argument. In MySQL the function is named LOG10
. MySQL also has an overload for the LOG
function which acts like Oracle’s LOG
function. SQL Server doesn’t have a function for base10 logarithms, but they can be calculated using the change of base identity: log_{10}A = log_{baseX}(A) / log_{baseX}(10)
.
The examples below use log_{10}1000, the value of which is 3 (10^{3} = 1,000). The changeofbase identity is also illustrated by calculating log_{10}1000 using base e (note that the changeofbase calculation has rounding issues in MySQL; the LOG10
function is the only one that returns exactly 3):
Database  Expression  Result 
Teradata  LOG(1000) 
3.0 
LN(1000) / LN(10) 
3.0 

Oracle  LOG(10, 1000) 
3 
LN(1000) / LN(10) 
3 

SQL Server  LOG(1000) / LOG(10) 
3 
MySQL  LOG10(1000) 
3 
LOG(10, 1000) 
2.9999999999999996 

LOG(1000) / LOG(10) 
2.9999999999999996 
NULLIFZERO Function
The NULLIFZERO
function returns null if the argument passed to it is zero. If the argument is any other value, it’s returned unchanged. It’s not ANSI compliant. This function useful for avoiding “divide by zero” errors. As an example, take the cost per widget query below:
SELECT cost / widgets FROM products;
If the widgets
value is zero for any row, Teradata will throw a “division by zero” error. Division by zero is undefined and so, in a way, is null, so the NULLIFZERO
function can be used to return a null value for rows with zero widgets:
SELECT cost / NULLIFZERO(widgets) FROM products;
For rows with zero widgets, the calculation becomes cost / NULL
, and any time null is in a calculation the result is null.
Database  Expression  Result 
Teradata  NULLIFZERO(0) 
null 
NULLIFZERO(5.2) 
5.2 

Teradata Oracle SQL Server MySQL 
NULLIF(0, 0) 
null 
NULLIF(5.2, 0) 
5.2 
LOG Function
The RANDOM
function returns a random integer between a specified lower and upper bound. This is not an ANSI standard function.
Oracle doesn’t have a builtin randomnumber generator, but random numbers can be generated using the DBMS_RANDOM
package. SQL Server has a RAND
function that will return a random number between zero and one, exclusive. MySQL also has a RAND
function but its return value is between zero inclusive and one exclusive – in other words, the SQL Server RAND
function will never return a zero but the MySQL RAND
function may return a zero.
The examples below return a random integer between 1 and 10:
Database  Expression  
Teradata  RANDOM(1, 10) 

Oracle  TRUNC(DBMS_RANDOM.VALUE * 10) + 1 

SQL Server  ROUND(RAND() * 10, 0, 1) + 1 

MySQL  TRUNCATE(RAND() * 10) + 1 
3 
Note that the SQL Server ROUND
function will truncate if its optional third parameter is a nonzero value.
RANGE_N Function
This function assigns a ranking to a range of values. It’s not ANSI compliant.
RANGE_N
is best explained with a few examples:
RANGE_N(expr BETWEEN 0, 20, 40 and 60)
: Returns 1 if
0 <= expr < 20
 Returns 2 if
20 <= expr < 40
 Returns 3 if
40 <= expr < 60
 Returns null if
expr
is not between 0 and 60  Returns null if
expr
is null
 Returns 1 if
RANGE_N(expr BETWEEN *, 0, 20, 40, 60 and *)
: Returns 1 if
expr < 0
 Returns 2 if
0 <= expr < 20
 Returns 3 if
20 <= expr < 40
 Returns 4 if
40 <= expr < 60
 Returns 5 if
expr > 60
 Returns null if
expr
is null
 Returns 1 if
RANGE_N(expr BETWEEN 0, 20, 40 and 60, NO RANGE, UNKNOWN)
: Returns 1 if
0 <= expr < 20
 Returns 2 if
20 <= expr < 40
 Returns 3 if
40 <= expr < 60
 Returns 4 if
expr
is not between 0 and 60 (NO RANGE
)  Returns 5 if
expr
is null (UNKNOWN
)
 Returns 1 if
RANGE_N(expr BETWEEN 0, 20, 40 and 60, NO RANGE OR UNKNOWN)
: Returns 1 if
0 <= expr < 20
 Returns 2 if
20 <= expr < 40
 Returns 3 if
40 <= expr < 60
 Returns 4 if
expr
is not between 0 and 60 or ifexpr
is null (NO RANGE OR UNKNOWN
)
 Returns 1 if
Note the similarities between the RANGE_N
and CASE_N
functions.
This function is not supported by Oracle, SQL Server, or MySQL, but the same results can be gotten with a searched CASE
function. The matched CASE
equivalent to RANGE_N(expr BETWEEN 0, 20, 40 and 60, NO RANGE, UNKNOWN)
is:
CASE WHEN expr >= 0 AND expr < 20 THEN 1 WHEN expr >= 20 AND expr < 40 THEN 2 WHEN expr >= 40 AND expr < 60 THEN 3 WHEN expr < 0 OR expr >= 60 THEN 4 ELSE 5 END
The WHEN
expressions cover all possible ranges, so the fallthrough of ELSE 5
will only catch a null. Another way to do this is:
CASE WHEN expr >= 0 AND expr < 20 THEN 1 WHEN expr >= 20 AND expr < 40 THEN 2 WHEN expr >= 40 AND expr < 60 THEN 3 WHEN expr IS NULL THEN 5 ELSE 4 END
These CASE
statements will also work in Teradata, leaving you to choose between CASE
(ANSI compliant and familiar to nonTeradata developers) or RANGE_N
(more compact).
SQRT
The SQRT
function returns the square root of a number. It's not ANSI compliant.
The function is identical in Oracle, SQL Server, and MySQL. Note that the argument to the function can't be negative, as the square root of a negative value isn't a real number.
SQRT(169)
→ 13SQRT(7)
→ 1.73205081SQRT(1)
→ causes an error
WIDTH_BUCKET Function
This ANSI SQL2003 compliant function returns the ntile partition for an expression. This example assigns quintiles for values from 0 to 100. If a value is less than zero its "bucket" will be 0, and if it's greater than 100 its bucket will be 6:
SELECT WIDTH_BUCKET(<em>expr</em>, 0, 100, 5) AS Quintile</code> FROM ...
Oracle supports this function; SQL Server and MySQL do not. For SQL Server and MySQL, use a searched CASE
statement:
SELECT CASE WHEN col1 < 0 THEN 0 WHEN col1 <= 20 THEN 1 WHEN col1 <= 40 THEN 2 WHEN col1 <= 60 THEN 3 WHEN col1 <= 80 THEN 4 WHEN col1 <= 100 THEN 5 ELSE 6 END AS Quintile FROM ...
The searched CASE
will also work in Teradata and Oracle.
Also note that Teradata, Oracle, and SQL Server support the analytic NTILE
function, which returns similar results.
ZEROIFNULL Function
The ZEROIFNULL
function takes one argument and returns zero if the argument is null or the argument's value if it's not null. It's not ANSI compliant.
Examples:
ZEROIFNULL(99)
→ 99ZEROIFNULL(null)
→ 0
The ZEROIFNULL
function isn't supported by Oracle, SQL Server, or MySQL. For these databases, use a searched CASE
instead (this also works in Teradata):
CASE WHEN val IS NULL THEN 0 ELSE val END;