The WIDTH BUCKET gives values to buckets in an equal-width histogram.

Format: WIDTH_BUCKET(expression, min, max, buckets)

Expression represents a value to be assigned to one of multiple buckets. This is normally applied to multiple row values. The range of values is defined by min (which is included in the buckets) and max (which is not included in the buckets). When the expression is less than min, zero (0) is returned. When the expression is greater than max, then max+1 is returned.

Example:      SELECT X, WIDTH_BUCKET(X, 1, 10, 5) AS 'Bucket' FROM TABLEA;

Result:

                    

> Access, DB2, MySQL, Oracle, PostgreSQL, and SQL Server do not support WIDTH_BUCKET.

he SQRT function returns the square root of a number.
Format: SQRT(expression)

Example:           SQRT(100)     Result: 10

                   SQRT(10000)   Result: 100

 

> DB2, MySQL, Oracle, PostgreSQL, and SQL Server support the POWER function.

> Access uses ^ (an up arrow) to raise a number to a power or exponent.

> Where 10 ^ 2 = 100, you can use 100 ^ (1/2) = 10 (the square root).
Alternatively, you could use 100 ^ .5 = 10.
1000 ^ (1/3) = 10 (the cube root).

 

 

The POWER function raises a number to a specific power or exponent.
Format: POWER(base, exponent)

Example:           POWER(10, 2) Result: 100

                   POWER(0, 0) Result: 1

                   POWER(10, -3) Result: .001

> DB2, Oracle, PostgreSQL, and SQL Server support the POWER function.

> Access uses ^ (an up arrow) to raise a number to a power or exponent.

> MySQL supports POW(base, exponent).

 

The POSITION function returns an integer indicating the first starting position of a string within the search string.
If the search string is not found, zero will be returned.
Format: POSITION(search_string IN full_string)

Example:           POSITION('CADAB' IN 'ABRAH CADABRAH') Result: 7

                   POSITION('CABAD' IN 'ABRAH CADABRAH') Result: 0

> Various RDBMS systems are case sensitive or case insensitive. See the documentation for your system.

> Access uses InStr(start_position, string, substring) to find the beginning and InStrRev() to find the ending count from the end.

> DB2 uses POSSTR(string, substring).

> MySQL supports POSITION.

> Oracle uses INSTR(string, substring).

> SQL Server uses CHARINDEX(substring, string).
SQL Server also uses PATINDEX which allows wildcard characters.

 

The OCTET_LENGTH function returns the number of octets (8 bits) within the string expression.
Format: OCTET_LENGTH(string)

Example:           SELECT CUST_NAME,
                          OCTET_LENGTH(CUST_NAME) AS 'OCTET_LENGTH'
                     FROM CUSTOMER_TBL;

Result:

                   

> The length of an empty string (’ ‘) is zero.
> Access, DB2, MySQL, Oracle, PostgreSQL, and SQL Server do not support OCTET_LENGTH.
> Access uses LEN(string) to find the number of characters.
> DB2 and Oracle use LENGTH(string) to find the number of characters.
> SQL Server uses DATALENGTH(string) to find the number of characters.
> MySQL uses BIT_COUNT(string) to find the number of bits.
> Oracle uses LENGTHB(string) to find the number of bits.

The MOD function returns the remainder of a dividend divided by a divisor.
Format:                        MOD(dividend, divisor)

Example:           SELECT MOD(10, 7) FROM TABLEA;

Result:

                   

> Access, DB2, MySQL, PostgreSQL, SQL Server, and Sybase support the MOD function.

The LN function returns the natural logarithm of a number, which is the power to which you would need to raise the constant e (approximately 2.718281828…) in order to get the result.
Format:                        LN(expression)

Example:           SELECT LN(2.718281) FROM TABLEA;

Result:

                   

> DB2, Oracle, and PostgreSQL support the LN function.

> DB2 and PostgreSQL also support LOG as a synonym.

> MySQL and SQL Server only support the non-standard LOG function.

> Use the EXP function to reverse the process.

 

he FLOOR function returns the largest integer less than the input value.
Format:                        FLOOR(expression)

Example:           SELECT FLOOR(10.8) FROM TABLEA;

Result:

                   

>DB2, MySQL, Oracle, and SQL Server support FLOOR.

> Access, PostgreSQL, and Sybase don’t support FLOOR.

 

The EXTRACT function extracts parts from a date and gives a datetime value.

Format: EXTRACT(date_part FROM expression)

Date_parts are:

CENTURY ................................. PostgreSQL .................
DAY ............. Access DB2 MySQL Oracle PostgreSQL SQL Server Sybase
DAY_HOUR ................... MySQL ...................................
DAY_MINUTE ................. MySQL ...................................
DAY_SECOND ................. MySQL ...................................
DAYOFYEAR .......................................... SQL Server ......
DECADE .................................. PostgreSQL .................
DOW (day of week) ....................... PostgreSQL .................
DOY (day of year) ....................... PostgreSQL .................
EPOCH ................................... PostgreSQL .................
HOUR ............ Access DB2 MySQL Oracle PostgreSQL SQL Server Sybase
HOUR_MINUTE ................ MySQL ...................................
HOUR_SECOND ................ MySQL ...................................
MICROSECONDS ........... DB2 ............ PostgreSQL .................
MILLENNIUM .............................. PostgreSQL .................
MILLISECONDS ............................ PostgreSQL SQL Server ......
MINUTE .......... Access DB2 MySQL Oracle PostgreSQL SQL Server Sybase
MINUTE_SECOND .............. MySQL ...................................
MONTH ........... Access DB2 MySQL Oracle PostgreSQL SQL Server Sybase
MONTHNAME ....... Access .............................................
QUARTER ................................. PostgreSQL SQL Server ......
SECOND .......... Access DB2 MySQL Oracle PostgreSQL SQL Server Sybase
TIMEZONE_ABBR .................... Oracle ............................
TIMEZONE_HOUR .................... Oracle PostgreSQL .......... Sybase
TIMEZONE_MINUTE .................. Oracle PostgreSQL .......... Sybase
TIMEZONE_REGION .................. Oracle ............................
WEEK ................... DB2 ..... Oracle PostgreSQL SQL Server ......
WEEKDAY ......... Access ........................... SQL Server ......
WEEKDAYNAME ..... Access .............................................
YEAR ............ Access DB2 MySQL Oracle PostgreSQL SQL Server Sybase
YEAR_MONTH ................. MySQL ...................................

Example: SELECT EXTRACT(DAY FROM ‘2056-01-11′) FROM TABLEA;

Result:

                   

> A date or time range returns all the values inbetween, such as DAY_SECOND gives DAY, HOUR, MINUTE, SECOND (11 09:15:00).
> MySQL, Oracle, PostgreSQL, and Sybase support EXTRACT.
> Access, and SQL Server do not support EXTRACT. Instead, they use DATEPART(date_part, expression).
Access also supports DATEADD, DATEDIFF, DATESERIAL, DATEVALUE, TIMESERIAL, and TIMEVALUE.
> DB2 does not support EXTRACT. Instead, it uses the date_part name as the functions, such as DAY(expression).
Other DB2 functions include LOCAL_TIME, and LOCAL_TIMESTAMP.
> MySQL also supports NOW().

The EXP function returns the value of the natural logarithm to the base e. The mathematical constant e is approximately 2.718281828… raised to the power of the specified value.
Format:                        EXP(expression)

Example:           SELECT EXP(1) FROM TABLEA;

Result:

                   

> Access, DB2, MySQL, PostgreSQL, SQL Server, and Sybase support the EXP function.

> Use the LN function to reverse the process.