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:
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%';
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;
Append the word "Currency" to the currencies (without trailing spaces):SELECT TRIM(TRAILING FROM currency) || ' Currency' FROM currencies WHERE code LIKE 'A%';
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;
Find the country that sounds the same as "ASTRALYA":SELECT country FROM countries WHERE SOUNDEX(country) = SOUNDEX('astralya');
Extract the first 5 characters from each format:SELECT SUBSTRING(format FROM 1 FOR 5) FROM formats;
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40