Home / Documentation / FAQ / Support / Download Internal SQL Functions

### Internal SQL Functions Index

> 1. Functions Introduction
> 2. Mathematical Functions
> 3. String Functions
> 4. Aggregate Functions
> 5. Security Functions
> 6. Branch Functions
> 7. Date/Time Functions
> 8. Misc 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, end_index)

> > Returns a substring of the string argument. This emulates the java.lang.String.substring methods except it is more forgiving of values that are outside the boundary of the string.

> > > Examples:

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

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

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

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

> > >          SELECT SUBSTRING('abcd', -4, 5000)
> > >     ==>  abcd

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

Last Updated: Mon Nov 18 14:36:46 PST 2002 Mckoi SQL Database Copyright © 2000, 2001, 2002 Diehl and Associates, Inc. All rights reserved.