Background documentationFunctions Locate this document in the navigation structure

 

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 can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

Function Descriptions in the Reference Manual

Examples of 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 functions

Conversion Function (conversion_function)

NUM, HEXTOROW, CHAR

Database Function Call (dbfunction_call)

Call of a database function defined using CREATE FUNCTION

See also: SQL Reference Manual, Function (dbfunction_call)

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

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

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 31.12.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 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 Information

More examples for Data Query