Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

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
Mimer SQL User's Manual TOC PREV NEXT INDEX