Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


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, Chapter 7, 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

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



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX