Internal SQL Functions Index

1. Functions Introduction

Functions may be used within all expressions except aggregate functions that may only be used within the SELECT .... FROM clause.

2. Mathematical Functions

Returns the absolute value of a number.

Examples:

     SELECT ABS(-0.94)
==>  0.94

     SELECT ABS(9 - 200)
==>  191

SIGN(number)

Returns 1 if the number is positive, -1 if the number is negative and 0 if the number is zero.

Examples:

     SELECT SIGN(40)
==>  1

     SELECT SIGN(-40)
==>  -1

     SELECT SIGN(40 - 40)
==>  0

MOD(number1, number2)

Returns the modulo of number1 and number2 (equivalent to {number1 % number2} in Java).

Examples:

     SELECT MOD(15, 5)
==>  0

     SELECT MOD(33, 10)
==>  3

     SELECT ROUND(552 / 10), MOD(552, 10)
==>  55, 2

ROUND(number, decimal_places)
ROUND(number)

Rounds the number to 'n' decimal places. When no 'decimal_places' argument is provided the number is rounded to the nearest whole number.
This will round up if the fraction to the right is >= .5 otherwise it rounds down. This uses the {BigDecimal.setScale(decimal_places, BigDecimal.ROUND_HALF_UP)} method for rounding.

Examples:

     SELECT ROUND((943 * 13) / 99, 3)
==>  123.828

     SELECT ROUND((943 * 13) / 99, 2)
==>  123.83

     SELECT ROUND((943 * 13) / 99)
==>  124

POW(number1, number2)

Raises number1 to the power of number2.

Examples:

     SELECT POW(9, 6)
==>  531441

     SELECT POW(2, 32)
==>  4294967296

     SELECT POW(2, 64)
==>  18446744073709551616

     SELECT POW(2, -3)
==>  0.125

SQRT(number)

Finds the square root of the number argument.

Examples:

     SELECT SQRT(65536)
==>  256

     SELECT SQRT(-1)
==>  NULL

LEAST(val1, val2, ...)

This function accepts any number of arguments and returns the value that represents the least value of the set.

Examples:

     SELECT LEAST(4)
==>  4

     SELECT LEAST(90, 9.125, 3, 75)
==>  3

     SELECT LEAST('H', 'Z', 'B')
==>  B

     SELECT LEAST(10 / 3, 10 * 3,
                  POW(10, 3), MOD(10, 3))
==>  1

GREATEST(val1, val2, ...)

This function accepts any number of arguments and returns the value that represents the greatest value of the set.

Examples:

     SELECT GREATEST(4)
==>  4

     SELECT GREATEST(90, 9.125, 3, 75)
==>  90

     SELECT GREATEST('H', 'Z', 'B')
==>  Z

     SELECT GREATEST(10 / 3, 10 * 3,
                     POW(10, 3), MOD(10, 3))
==>  1000

3. String Functions

LOWER(str)

Returns a lower case version of the string literal argument.

Examples:

     SELECT LOWER('THis is sOME TEXT')
==>  this is some text

UPPER(str)

Returns an upper case version of the string literal argument.

Examples:

     SELECT UPPER('THis is sOME TEXT')
==>  THIS IS SOME TEXT

CONCAT(str1, str2, ...)

Returns the concatenation of the string arguments. This function can take any number of arguments.

Examples:

     SELECT CONCAT('This i', 's some text', '.')
==>  This is some text.

     SELECT CONCAT('-', 0.95)
==>  -0.95

LENGTH(str)

Returns the number of characters in the string argument.
NOTE: This may additionally be used on BLOB data to return the count of bytes in the BLOB.

Examples:

     SELECT LENGTH('This is some text')
==>  17

     SELECT LENGTH(0.544)
==>  5

     SELECT LENGTH('    Test')
==>  8

TRIM( [ [ LEADING | TRAILING | BOTH ] [ characters ] FROM ] str )
LTRIM(str)
RTRIM(str)

Trims characters from a string argument. The LTRIM and RTRIM form trim whitespace from the left and right of the string respectively.

Examples:

     SELECT TRIM(TRAILING 'a' FROM 'aaabcdaaa')
==>  aaabcd

     SELECT TRIM(LEADING 'a' FROM 'aaabcdaaa')
==>  bcdaaa

     SELECT TRIM('ab' FROM 'ababzzzzab')
==>  zzzz

     SELECT TRIM('  a string message ')
==>  a string message

SUBSTRING(str, start_index)
SUBSTRING(str, start_index, length)

Returns a substring of a string. The SUBSTRING function complies with the SQL specification. The start_index parameter is a value between 1 and the length of the string where 1 includes the first character, 2 includes the second character, etc. The length parameter represents the size of the substring.

Examples:

     SELECT SUBSTRING('Tobias Downer', 8)
==>  Downer

     SELECT SUBSTRING('abcd', 1, 2)
==>  ab

     SELECT SUBSTRING('abcd', 3, 4)
==>  cd

     SELECT SUBSTRING('abcd', 3, 5000)
==>  cd

     SELECT SUBSTRING('abcd', 0, 5000)
==>  abcd

     SELECT SUBSTRING('abcd', 1, 0)
==>  (string of 0 length)

4. Aggregate Functions

Aggregate functions can only operate within a group of a SELECT statement. They are used to compute statistics over a set of records.
COUNT(*)
COUNT(DISTINCT expression_list)
COUNT(column_name)
COUNT(expression)

The * version of this function returns the total number of rows in the group. If a column name is specified it returns the number of non-null values in the group. The 'expression' form of this function evaluates the expression for each row in the group and counts it only if it evaluates to NULL. COUNT(DISTINCT ... ) counts all distinct values of the expression list over the group.

Examples:

     SELECT COUNT(*)
       FROM Orders

     SELECT COUNT(*)
       FROM Orders
   GROUP BY division

     SELECT COUNT(id)
       FROM Orders
   GROUP BY division

     SELECT last_name, COUNT(DISTINCT last_name)
       FROM Customers
   GROUP BY age

SUM(column_name)
SUM(expression)

Calculates the sum of all values in a column/expression over a group. The expression form of this function is evaluated for each row in the group.

Examples:

     SELECT SUM(value) FROM Orders

     SELECT SUM(quantity * value)
       FROM Orders

     SELECT SUM(quantity * value) * 0.75
       FROM Orders
   GROUP BY division

AVG(column_name)
AVG(expression)

Calculates the average of the column/expression over the group. The expression form of this function is evaluated for each row in the group.

Examples:

     SELECT AVG(value) FROM Orders

     SELECT AVG(quantity * value)
       FROM Orders

     SELECT AVG(quantity * value) * 0.75
       FROM Orders
   GROUP BY division

MIN(column_name)
MIN(expression)

Finds the minimum value of a column/expression over a group.

Examples:

     SELECT MIN(value) FROM Orders

     SELECT MIN(quantity * value)
       FROM Orders

     SELECT MIN(quantity * value) * 0.75
       FROM Orders
   GROUP BY division

MAX(column_name)
MAX(expression)

Finds the maximum value of a column/expression over a group.

Examples:

     SELECT MAX(value) FROM Orders

     SELECT MAX(quantity * value)
       FROM Orders

     SELECT MAX(quantity * value) * 0.75
       FROM Orders
   GROUP BY division

5. Security Functions

Functions that provide security information about the session performing the query.
USER()

Returns the current user.

PRIVGROUPS()

Returns a comma deliminated list of priv groups the user belongs to. A user may belong to any number of groups which dictate the tables a user may access.

6. Branch Functions

IF(condition_expr, true_expr, false_expr)

If the first expression (condition_expr) evaluates to true this function returns the result of 'true_expr' otherwise returns the result of 'false_exp'.

Examples:

     SELECT IF(true, 5, 8)
==>  5

     SELECT IF(false, 5, 8)
==>  8

     SELECT IF(NULL, 5, 8)
==>  NULL

     SELECT IF(true, IF(false, 1, 2), 3)
==>  2

     SELECT IF(col1 = 0, 'N/A', col1) FROM MyTable

COALESCE(expr1, expr2, expr3, ....)

Returns the first non null value from the parameters or null if the entire list contains null values.

Examples:

     SELECT COALESCE(NULL, 'a')
==>  a

     SELECT COALESCE(NULL, NULL, NULL)
==>  NULL

     SELECT COALESCE(col1, 'N/A') FROM MyTable

7. Date/Time Functions

DATEOB(date_string)

Parses a string to a Date object that can be used on queries against TIMESTAMP / DATE / TIME columns. DATEOB with no arguments returns the current time of the machine running the database.
Since version 0.92 this function has been deprecated. Use the standard DATE, TIME and TIMESTAMP literals specified in SQL-92 instead.

Examples:

     SELECT DATEOB()
==>  Wed Aug 09 11:49:31 EDT 2000

     SELECT DATEOB('Aug 1, 2000')
==>  Tue Aug 01 00:00:00 EDT 2000

     SELECT number FROM Orders
      WHERE date_made >= DATEOB('Jan 1, 2000')

8. Misc Functions

UNIQUEKEY(table_name)

Returns a unique key for the given table name. This is an atomic operation that is guaranteed to return a unique number each call. It should be used to generate unique identification numbers for records. It is similar to the AUTO_INCREMENT feature of other database systems.

Examples:

     SELECT UNIQUEKEY('Orders')

     INSERT INTO Orders
        ( id, number, division, date_made, quantity,
          value )
       VALUES
        ( UNIQUEKEY('Orders'), CONCAT('Order-', id),
          'Bio Engineering', DATEOB(), 25, 1900.00 )

TONUMBER(expression)

Attempts to cast the expression to a number. If the expression is a boolean then this function will return 1 for true or 0 for false. If the expression is a String then it attempts to parse the string into a number. If the expression is a Date then it returns the date as the number of milliseconds since Jan 1st, 1970.

Examples:

     SELECT TONUMBER(DATEOB('Aug 1, 2000'))
==>  965102400000