The OVERLAY function changes all characters to lower case. The embedded_string replaces the length in string starting at character position start. If length is not specified, then the embedded_string will replace all characters after start in string.
Format: OVERLAY(string PLACING embedded_string FROM start [FOR length])

Example:           OVERLAY('Dare to be creative' PLACING 'great' FROM 12)

Result: ‘Dare to be great’
> PostgreSQL supports OVERLAY.

> Access, DB2, MySQL, and SQL Server do not support OVERLAY. Instead they use REPLACE.

> Oracle does not support OVERLAY. Instead it uses REGEXP_REPLACE.

The LOWER function changes all characters to lower case. Case changes affect only letters. Spaces, numbers, special characters, and punctuation remain unchanged.
Format: LOWER(string)

Example:           LOWER('Look at THAT!')

Result: ‘look at that!’
> Access does not support LOWER. Instead it uses LCASE.

> DB2, and MySQL, support LOWER as well as LCASE.

> Oracle, PostgreSQL, and SQL Server support LOWER.

The CONVERT function changes the representation of a character string within its character set.

 

Format: CONVERT(value, USING conversion_char_name)

 

> Access supports CONVERT with a very different format.

> Oracle supports CONVERT but the format is CONVERT(value, target_char_set, source_char_set).

> PostgreSQL supports CONVERT and conversions can be defined using CREATE CONVERSION.

> SQL Server’s CONVERT is very different and is similar to the CAST function.

> DB2, and MySQL, do not support CONVERT.

If we have a table: ACCOUNTS_PAYABLE_TBL

Return VNDR_ID, INVN_ID, AMT_DUE, and then place ‘–> ‘ ahead of the NOTES and ‘ <–’ behind the NOTES column.

          SELECT VNDR_ID,
                 INVN_ID,
                 AMT_DUE,
                 '--> ' || NOTES || ' <--'
            FROM ACCOUNTS_PAYABLE_TBL;

Result Table:

 The trailing constant ‘ <–’ “floated” to the left behind the NOTES value
because the NOTES column is defined as VARCHAR.

If we have a table: VENDOR_TBL

If you concatenate the STATE and the COUNTRY columns, you get the following results:

          SELECT STATE || COUNTRY
            FROM VENDOR_TBL;

Normally-defined character values are padded with spaces to the
length of the column definition.
The concatenation of multiple character columns does not add or
eliminate trailing spaces.
If the column is defined as variable (VARCHAR), then the length
is determined by the inserted value which may or may not
include leading or trailing spaces.

Result Table:

                    

If we have a table: VENDOR_TBL

The boss wants a list of vendor IDs (VNDR_ID) and
VENDOR_NAMEs from the VENDOR_TBL. But instead of
V001, V002, … he wants VENDOR001, VENDOR002, etc.

SELECT ‘VENDOR’
|| SUBSTRING(VNDR_ID, 2, 3),
VENDOR_NAME
FROM VENDOR_TBL;

The SUBSTRING function converts V001, V002, … to 001, 002, …
(substring from position 2 for a length of 3). Then the || (concatenation) adds ‘VENDOR’ to the value making ‘VENDOR001′, ‘VENDOR002′, etc. See SUBSTRING.

Result Table:

 

Two alphanumeric character values (or strings) can be attached together (concatenated) to form one value
(or a single string). We do this by using the concatenation symbol || which is two double-bar characters (two key strokes). Only string values can be concatenated.

Format: expression || expression

Example:      'Concat' || 'enation' -> becomes -> 'Concatenation'

> Concatenation does not add a space between the strings.
> Multiple concatenations may be performed. Such as ‘a’ || ‘b’ || ‘c’ becomes ‘abc’.
> Concatenation of an empty string adds nothing to the result. Such as ‘a’ || ” || ‘b’ || ” || ‘c’ becomes ‘abc’.
> If a NULL is used as a string, the result is a NULL. ‘a’ || NULL || ‘b’ || ‘c’ becomes NULL.
> You can concatenate hexadecimal and bit strings. Such as B’0000′ || B’1111′ becomes B’00001111′.
> If you wish to add a non-character value to a string, then that value must be converted to a character
string before the concatenation can be performed.
> The || can be used in SELECT, WHERE, and ORDER BY or anywhere an expression is allowed.
> Oracle treats a NULL as an empty string. Such as ‘Concat’ || NULL || ‘enation’ becomes ‘Concatenation’.
> In MySQL, the || symbol is illegal. MySQL uses CONCAT(). Such as CONCAT(’Concat’, ‘enation’).
> Access and SQL Server uses + between character strings for concatenation. Such as ‘Concat’ + ‘enation’.
> MySQL, Oracle, and PostgreSQL automatically convert non-string data to strings.

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).