Using Scalar Functions

---

Scalar functions operate on expressions or on a single value received from a SELECT statement.

Some of the scalar functions available are:

Scalar function	Description
CHAR_LENGTH	returns the length of a string.
EXTRACT	returns a single field from a DATETIME or INTERVAL value.
LOWER	converts all upper case letters in a character string to lower case.
POSITION	returns the starting position of the first occurrence of a specified string expression, starting from the left, in the given character string.
SOUNDEX	returns a character string containing six digits which represents an encoding of the sound of the given character string.
SUBSTRING	extracts a substring from a given string, according to specified start position and length of the substring.
TRIM	removes leading and/or trailing instances of a specified character from a string.
UPPER	converts all lower case letters in a character string to upper case.

The complete list of scalar functions can be found in the Mimer SQL Reference Manual, About Scalar Functions.

Examples of Scalar Functions

The following are examples that illustrate how the scalar functions may be used:

List all currencies that contain the letters "AU" in upper or lower case:

 SELECT currency
    FROM currencies
    WHERE LOWER(currency) LIKE '%au%';

Returns:

CURRENCY
Australian Dollars
Mauritius Rupees
Saudi Riyals

Note: Alternatively, a case insensitive collation can be used to get the same result.

 SELECT currency
    FROM currencies
    WHERE currency COLLATE english_1 LIKE '%au%';

Find the position of the first space character in the formats column:

 SELECT format, POSITION(' ' IN format)
    FROM formats;

Returns:

FORMAT
Audio CD	6
Cassette	0
DVD Audio	4
Vinyl	0
Audio Cassette	6
Audio CD	6
Hardcover	0
Paperback	0
DVD Video	4
Video	0

Append the word "Currency" to the currencies (without trailing spaces):

 SELECT TRIM(TRAILING FROM currency) || ' Currency'
    FROM currencies
    WHERE code LIKE 'A%';

Returns:

UAE Dirhams Currency
Afghanis Currency
Leke Currency
Armenian Drams Currency
Netherlands Antillian Guilders Currency
Kwanza Currency
...

Remove both leading and trailing spaces from the currencies and convert to uppercase; and get the significant length (in characters):

 SELECT UPPER(TRIM(currency)), CHAR_LENGTH(TRIM(currency))
       FROM currencies;

Returns:

UAE DIRHAMS	11
AFGHANIS	8
LEKE	4
ARMENIAN DRAMS	14
NETHERLANDS ANTILLIAN GUILDERS	30
KWANZA	6
...	...

Find the country that sounds the same as "ASTRALYA":

 SELECT country
    FROM countries
    WHERE SOUNDEX(country) = SOUNDEX('astralya');

Returns:

COUNTRY
Australia

Extract the first 5 characters from each format:

 SELECT SUBSTRING(format FROM 1 FOR 5)
    FROM formats;

Returns:

FORMAT
Audio
Casse
DVD A
Vinyl
Audio
Audio
Hardc
Paper
DVD V
Video

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se