Internal SQL Functions

1. Functions Introduction
This section gives examples of all functions that are available in the Mckoi database software. Functions may be used within all expressions except aggregate functions that may only be used within the SELECT .... FROM clause.

2. Mathematical Functions
Follows are all the mathematical functions available in Mckoi Database. Note that some of these functions may lose precision because the number could be cast to a 'double'. You can assume that all mathematical functions have at worst the precision of a 'double'. ABS(number)
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

String functions in Mckoi Database mostly map to equivalent functions found within java.lang.String. 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