Using Scalar Functions
Scalar functions operate on expressions or on a single value received from a
Some of the scalar functions available are:
returns the length of a string.
returns a single field from a
converts all upper case letters in a character string to lower case.
returns the starting position of the first occurrence of a specified string expression, starting from the left, in the given character string.
returns a character string containing six digits which represents an encoding of the sound of the given character string.
extracts a substring from a given string, according to specified start position and length of the substring.
removes leading and/or trailing instances of a specified character from a string.
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, 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