www.mimer.com/developer

Using Scalar Functions

---

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

Some of the standard 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. Also consult the Mimer SQL Reference Manual for the syntax rules and for information regarding the data type of the result of the scalar functions.

Examples of Scalar Functions

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

List all hotels with name Winston, spelled with either upper or lower case letters:

 SELECT  NAME,CITY
 FROM    HOTEL
 WHERE   UPPER(NAME) = 'WINSTON';

Result:

NAME	CITY
Winston	London
WINSTON	COPENHAGEN
WINSTON	GOTHENBURG

List all double rooms at hotel SKY:

 SELECT  ROOMNO,ROOMTYPE
 FROM    ROOMS
 WHERE   SUBSTRING(ROOMTYPE FROM 3 FOR 3) = 'DBL'
 AND     HOTELCODE = 'SKY';

Result:

ROOMNO	ROOMTYPE
SKY121	NSDBLS
SKY124	NSDBLB
SKY125	NSDBLB
SKY212	NSDBLB

Get name and address (without trailing blanks) of guest with reservation number 1348:

 SELECT  TRIM(TRAILING FROM GUEST_LNAME) ||
         ', ' ||
         TRIM(TRAILING FROM ADDRESS)
 FROM    BOOK_GUEST
 WHERE   RESERVATION = 1348;

Result:

JOHANSEN, MIMERGATAN 4, UPPSALA

Remove leading and trailing spaces and get length (no. of characters) of description and the description (in lower case) for all charges:

 SELECT  CHAR_LENGTH(TRIM(DESCRIPTION)), LOWER(TRIM(DESCRIPTION))
 FROM    CHARGES;

Result:

7	lodging
9	telephone
8	car park
10	restaurant
7	minibar
3	bar
12	room service
7	laundry
4	room
9	extra bed
13	miscellaneous

List all the guest names that sounds like 'Johnson':

 SELECT  GUEST_LNAME
 FROM    BOOK_GUEST
 WHERE   SOUNDEX(GUEST_LNAME) = SOUNDEX('JOHNSON');

Result:

JANSSON

JONSON

JOHNZON

Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.se