Functions 

You can use expressions with functions. These functions can be applied to the data in the results table or specified in search conditions.

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

Explanations

Function descriptions in the Reference Manual	Examples for this function group
Arithmetic Function (arithmetic_function)	TRUNC, SIGN, ABS
Trigonometric Function (trigonometric_function)	SIN, ATAN, DEGREES
String Function (string_function)	SUBSTR, UPPER, MAPCHAR
Date Function (date_function)	SUBDATE, DAYOFMONTH, DAYNAME
Time Function (time_function)	ADDTIME, TIMEDIFF, MAKETIME
Extraction (extraction_function)	DAY, TIMESTAMP, DATE
Special Function (special_function)	VALUE, LEAST, CASE function
Conversion Function (conversion_function)	NUM, HEXTOROW, CHAR
Database Function Call (dbfunction_call)	Call of a database function defined using CREATE FUNCTION

Most of these functions are self-explanatory. For this reason, examples of just a few selected functions will be given here.

See also:

SQL Reference Manual, Expression (expression)

SQL Reference Manual, Function (dbfunction_call)

 

String Functions

You can use the string functions to change the display of character strings.

 

SELECT cno, name & ', ' & firstname name
  FROM hotel.customer WHERE cno = 3000

First and last names are linked to one another.

Result

CNO	NAME
3000	Porter, Jenny

See also:

SQL Reference Manual, Concatenation (concatenation)

 

SELECT cno, SUBSTR(firstname,1,1) & '. ' & name name
  FROM hotel.customer WHERE cno = 3000

First and last names are linked to one another; the first name is displayed in initials only.

Result

CNO	NAME
3000	J. Porter

See also:

SQL Reference Manual, SUBSTR(x,a,b)

 

Date Functions

Date functions are applied to date and timestamp values or return a date or timestamp value as a result.

 

SELECT rno, arrival, DAYNAME(arrival) arrival_day FROM hotel.reservation WHERE rno < 150

Displaying the day of arrival

Result

RNO	ARRIVAL	ARRIVAL_DAY
100	2004-11-13	Saturday
110	2004-12-24	Friday
120	2004-11-14	Sunday
130	2005-02-01	Tuesday
140	2004-04-12	Monday

See also:

SQL Reference Manual, DAYNAME/MONTHNAME(t)

 

Extractions

You can use extraction functions to display parts of date, time, or timestamp values or to create date, time, or timestamp values.

 

SELECT name, 675456 number_of_days, DATE(675456) counted_date FROM hotel.city WHERE zip = '12203'

Displaying when 675456 days after 12/31/0000 have passed

Result

NAME	NUMBER_OF_DAYS	COUNTED_DATE
Albany	675456	1850-05-03

See also:

SQL Reference Manual, DATE(a)

 

General CASE Function

The general CASE function analyzes a sequence of search conditions and delivers a result for each search condition.

 

SELECT hno,
    CASE
      WHEN price IS NULL THEN 'Not yet priced'
      WHEN price < 70 THEN 'Very cheap'
      WHEN price >= 70 AND price < 100 THEN 'Normal price'
      ELSE 'Expensive hotel'
    END price_range
  FROM hotel.room WHERE hno < 100 AND type = 'single'

Estimating the price classes of single rooms in a selection of hotels

Result

HNO	PRICE_RANGE
10	Expensive hotel
20	Normal price
30	Very cheap
40	Normal price
50	Expensive hotel
60	Expensive hotel
70	Expensive hotel
80	Normal price
90	Normal price

See also:

SQL Reference Manual, General CASE Function (searched_case_function)

 

Simple CASE Function

The simple CASE function compares an expression with a sequence of simple expressions to determine in each case a result expression.

 

SELECT hno,
    CASE price
      WHEN NULL THEN 'Not yet priced'
      WHEN 70 THEN 'Very cheap'
      WHEN 90 THEN 'Cheap'
      WHEN 100 THEN 'Normal hotel'
      WHEN 115 THEN 'Expensive hotel'
      WHEN 135 THEN 'Very expensive hotel'
      ELSE 'Not yet in price_range list'
    END price_range
  FROM hotel.room WHERE hno < 100 AND type = 'single'

Comparing the prices of a selection of hotels with a rating scale for single room prices

Result

HNO	PRICE_RANGE
10	Very expensive hotel
20	Very cheap
30	Not yet in price_range list
40	Not yet in price_range list
50	Not yet in price_range list
60	Not yet in price_range list
70	Expensive hotel
80	Cheap
90	Cheap

See also:

SQL Reference Manual, Simple CASE Function (simple_case_function)

 

Database Function

Before you can execute this example, you have to create the database function avgprice(zip), as described in Data Definition ® Database Functions.

SELECT hno, avgprice(zip) avgprice FROM hotel.hotel WHERE hno = 40

Result

HNO	AVGPRICE
40	87.5

See also:

SQL Reference Manual, Database Function Call (dbfunction_call)

More examples for Data Query