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

Example:           UPPER('What is THAT?')

Result: ‘WHAT IS THAT?’

> Access does not support UPPER. Instead it uses UCASE.

> DB2 and MySQL support UPPER as well as UCASE.

> Oracle, PostgreSQL, and SQL Server support UPPER.

The TRIM function removes leading characters, trailing characters, or both from a character string or BLOB value.
If a removal character is not specified, spaces are removed by default. TRIM will not remove characters within a string. The COLLATE clause forces the result set into another pre-existing collating sequence.

Format:

TRIM([[{LEADING|TRAILING BOTH}] [remove_char] FROM source_string [COLLATE name])

Example:

TRIM(LEADING 'My name is ' FROM 'My name is William Diamond') Result: 'William Diamond'

TRIM(BOTH '---' FROM '---One Thousand Dollars---') Result: 'One Thousand Dollars'

TRIM(TRAILING 'Dollars---' FROM '---One Thousand Dollars---') Result: '---One Thousand '

 

> MySQL, Oracle, and PostgreSQL support TRIM.

> The Access functions, TRIM(string), LTRIM(string), and RTRIM(string) are non-standard according to
ANSI standards.

> DB2 and SQL Server do not support TRIM. Instead they have LTRIM(string) and RTRIM(string).

> Oracle and PostgreSQL also have LPAD and RPAD to add spaces.

 

The TRANSLATE function changes the representation of a character string from one base character set to another character set and collation.

Format: TRANSLATE(value, USING translation_char_name)

> Access does not support TRANSLATE.

> DB2 does not support the TRANSLATE standard. It has TRANSLATE(source, replacement, match).

> MySQL does not support TRANSLATE. Instead, it uses the CONV function.

> Oracle supports the TRANSLATE standard, but it can only convert between the database character set and the national language character set.

> PostgreSQL does not support TRANSLATE. Instead, it uses its own non-standard TRANSLATE function.

> SQL Server does not support TRANSLATE. Instead, it uses its own non-standard CONVERT function.

The SUBSTRING function allows a shorter string to be extracted from a longer string. The result is extracted from the source_string, starting in position, start_position (an integer value), for the number of characters, length (an integer value, if specified). The COLLATE clause forces the result set into another pre-existing collating sequence.

Format: SUBSTRING(source_string FROM start_position [FOR length] [COLLATE name])

Example:           SUBSTRING('My name is William Diamond' FROM 12)

Result: ‘William Diamond’

> If the input string is NULL, then the result will be NULL.
> Access does not support SUBSTRING. Instead it uses MID(string, start [, length]) and SPLIT.
> DB2 does not support SUBSTRING. Instead it uses SUBSTR(string, start [, length]).
> MySQL uses only SUBSTRING(source_string FROM start_position) and assumes that the substring will be from the start_position to the end of the string.
> Oracle does not support SUBSTRING. It uses SUBSTR(string, start [, length]). If a negative number is entered for start, then Oracle counts backwards from the end of the string. With a negative start and with no length, it extracts all the way to the beginning. Oracle treats an empty string as NULL. SUBSTR(NULL, 1, 1) results in ‘ ‘ (an empty string).
> PostgreSQL supports SUBSTRING, but does not support COLLATE.
> SQL Server supports SUBSTRING(string, start, length), but does not support COLLATE.

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: