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:
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';
List all double rooms at hotel SKY:SELECT ROOMNO,ROOMTYPE FROM ROOMS WHERE SUBSTRING(ROOMTYPE FROM 3 FOR 3) = 'DBL' AND HOTELCODE = 'SKY';
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;
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;
List all the guest names that sounds like 'Johnson':SELECT GUEST_LNAME FROM BOOK_GUEST WHERE SOUNDEX(GUEST_LNAME) = SOUNDEX('JOHNSON');
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40