SQL use of functions

Functions are used in SQL as they are in many languages to manipulate, modify, format, convert and calculate with data. Officially, Oracle functions are accepting arguments and returning values. Functions can be the kind that operates on each row individually, effecting the display of data for that row or functions that operate on groups. Functions that work with characters, either convert or manipulating are called character functions. This handout will deal with some of the character, numeric, and date functions available in Oracle.

Character Functions:

Character Functions Processing
INITCAP Converts the first alphabetic character of each word to uppercase
UPPER Converts alphabetic characters to upper case
LOWER Converts alphabetic characters to lower case
LTRIM Trims blank characters to the left
RTRIM Trims blank characters to the right
LPAD Pads the right with a string until n characters are reached
LPAD(data/field, n, string)
RPAD Pads the right with a string until n characters are reached
RPAD(data/field, n, string)
CONCAT Concatenates two fields - similiar to ||
CONCAT(data/field, data/field)
SUBSTR Returns a substring of the original starting at character m, for n characters (if no n, all characters till the end are returned)
SUBSTR(data/field, m [,n])
INSTR Finds a particular characters, m and returns it's position
INSTR(data/field, m)
LENGTH Returns the number of characters in a the data or field
REPLACE Replace all occurrences of str1 in the data/field with str2
REPLACE(data/field, str1, str2)
SOUNDEX Used to look for a column that sounds like the data/field entered

In the first examples I am looking at INITCAP, UPPER and LOWER. In these examples, the word dog is capitalized, the word dog is converted to upper case and the word DOG is converted to lower case. In the example involving the donor table, name is converted to upper case, stadr is converted to lower case, and state is converted to an initial capital. Notice the word in the FROM clause of these selects. DUAL is a system file that contains a 1 character dummy field. It can be used when you want to do a select and see the results but you do not want to do it on a specific table. To see DUAL, you can use the DESCRIBE or DESC command to show it. Note: Dual can also be refered to as SYS.DUAL.

SQL CODE:

SQL> desc dual;

 Name                            Null?    Type
 ------------------------------- -------- ----
 DUMMY                                    VARCHAR2(1)
Now back to the SQL code for INITCAP, UPPER and LOWER.

SQL CODE:

SQL> SELECT INITCAP('dog')
  2  FROM dual;

INI
---
Dog

SQL> SELECT UPPER('dog') 
  2  FROM dual;

UPP
---
DOG

SQL> SELECT LOWER('DOG')
  2  FROM dual;

LOW
---
dog
 
SQL> SELECT UPPER(name), LOWER(stadr), INITCAP(state) 
  2  FROM donor;

UPPER(NAME)     LOWER(STADR)    IN
--------------- --------------- --
STEPHEN DANIELS 123 elm st      Ma
JENNIFER AMES   24 benefit st   Ri
CARL HERSEY     24 benefit st   Ri
SUSAN ASH       21 main st      Ma
NANCY TAYLOR    26 oak st       Ma
ROBERT BROOKS   36 pine st      Ma

6 rows selected.
Frequently upper and lower can be used to make a more effective search when you are not sure in which format the data is stored. For example, if I wanted to find the town of Seekonk and I didn't know whether it was input as seekonk, SEEKONK, or Seekonk, I could do the following test.

SELECT city, state
FROM donor
WHERE UPPER(city) = 'SEEKONK';

I could also have used the WHERE LOWER(city) = 'seekonk' or WHERE INITCAP(city) = 'Seekonk' to accomplish the same goals.

In the following examples, I am using some of the other SQL functions to manipulate data for desired results. In the first example I am displaying cat with right spaces after it and dog with left spaces in front of it. I then trim cat using RTRIM and dog using LTRIM to get the word without spaces. Trim removes spaces and returns just the character data.

SQL CODE:

  1  SELECT 'cat      ','      dog', RTRIM('cat      '), LTRIM('      dog')
  2* FROM dual;
SQL> /

'CAT'     'DOG'     RTR LTR
--------- --------- --- ---
cat             dog cat dog
Padding fills the field with a designated character either to the left or the right depending on whether the instruction is RPAD or LPAD. In this example, I padded the right of city with * and the left of yrgoal with * (Note: I could have used any character). The format is LPAD/RPAD (the total length including the padding, the character to use for the padding).

SQL CODE:

SQL> SELECT RPAD(city,12,'*'), LPAD(yrgoal,8,'*')
  2  FROM donor;

RPAD(CITY,12 LPAD(YRG
------------ --------
Seekonk***** *****500
Providence** *****400
Providence**
Fall River** *****100
Fall River** ******50
Fall River** ******50

6 rows selected.
The following shows another method of concatenating data. Originally, we used the symbol ||, in this method the CONCAT function concatenates the two fields together. In the example below, I concatenated two literals and then two columns on the donor table.

SQL CODE:

SQL> SELECT CONCAT('dog','cat'), CONCAT(city,state)
  2  FROM donor;

CONCAT CONCAT(CITY,
------ ------------
dogcat SeekonkMA
dogcat ProvidenceRI
dogcat ProvidenceRI
dogcat Fall RiverMA
dogcat Fall RiverMA
dogcat Fall RiverMA

6 rows selected.
SUBSTR is an important function which allows me to separate part of a field out from the whole. When used in conjunction with INSTR which allows you to find a particular character in a field and LENGTH which shows the length of the string, REPLACE which allows for the replacement of one string of data by another, the person coding in SQL has a lot of power to manipulate data. In the example using SUBSTR, I wanted to separate out the month from the date and display it as a separate field. The format is SUBSTR(the data or column, the character to start with, the length to work with). In this case I am working with the field datefst, I want to start with the fourth character and take the next three.

SQL CODE:

SQL> SELECT SUBSTR(datefst,4,3), datefst
  2  FROM donor;

SUB DATEFST
--- ---------
JUL 03-JUL-98
MAY 24-MAY-97
JAN 03-JAN-98
MAR 04-MAR-92
MAR 04-MAR-92
APR 04-APR-98

6 rows selected.
The INSTR function allows me to locate a particular character in a data string or data in a column. In the example shown, I want to find the lower case letter e in the city column. The format is INSTR(the data or column being used, the character you are looking for).

SQL> SELECT city, INSTR(city,'e')
  2  FROM donor;

CITY       INSTR(CITY,'E')
---------- ---------------
Seekonk                  2
Providence               7
Providence               7
Fall River               9
Fall River               9
Fall River               9

6 rows selected.
Using LENGTH, I can find the number of characters in a string or in a column. In this example, I am looking for the number of characters in a name. To do that simply put the data string or name of the column in parenthesis after the function LENGTH.

SQL CODE:

SQL> SELECT name, LENGTH(name)
  2  FROM donor;

NAME            LENGTH(NAME)
--------------- ------------
Stephen Daniels           15
Jennifer Ames             13
Carl Hersey               11
Susan Ash                  9
Nancy Taylor              12
Robert Brooks             13

6 rows selected.
The REPLACE allows the user to replace a particular string of data in a column with another string of data. For example, if you changed a code and you want to replace all AB embedded in the inventory code to XY. This could be done with REPLACE(INV_CODE, 'AB','XY'). In the example below, I am replacing a whole field by replacing MA with TX.

SQL CODE:

SQL> SELECT REPLACE(state,'MA','TX')
  2  FROM donor;

REPL
----
TX
RI
RI
TX
TX
TX
Finally, the SOUNDEX function allows you to look for something that sounds like something else. This would be valuable when you don't know exactly what the correct spelling is. In my example, I am looking for the town of seekonk, but I only know what it sounds like.

SQL CODE:

SQL> SELECT city
  2  FROM donor
  3  WHERE soundex(city) = soundex('Seaconc');

CITY
----------
Seekonk

NUMERIC FUNCTIONS:

Numeric Functions Processing
ROUND Rounds numeric data
TRUNC Truncates numeric data
MOD Returns the numeric remainder that results from a divide
CEIL Finds nearest whole number greater then or equal to the data/field being tested
CEIL(data/field)
FLOOR Finds nearest whole number less than or equal to the data/field being tested
FLOOR(data/field)
POWER Exponentiation - where m is the number and n is the power
Power(m,n)
SQRT Square root of n
SQRT(data/field)
SIGN Returns 0 if data 0, 1 if data >0 and -1 if data <0
SIGN(data)


When working with numbers the ROUND function is used to round a number to a certain number of digits/decimals and the TRUNC function is used to truncate a number to a certain number of digits/decimals. The format is ROUND or TRUNC and then in parenthesis the number or field, the number of digits). In the first example, I rounded and truncated a number to two decimal places using dual. In the second example, I rounded and truncated the result of dividing yrgoal by 12 to three decimal places.

SQL CODE:

SQL> SELECT ROUND(123.456,2), TRUNC(123.456,2)
  2  FROM dual;

ROUND(123.456,2) TRUNC(123.456,2)
---------------- ----------------
          123.46           123.45

SQL> SELECT ROUND(yrgoal/12,3), TRUNC(yrgoal/12,3), yrgoal/12
  2  FROM donor;

ROUND(YRGOAL/12,3) TRUNC(YRGOAL/12,3) YRGOAL/12
------------------ ------------------ ---------
            41.667             41.666 41.666667
            33.333             33.333 33.333333

             8.333              8.333 8.3333333
             4.167              4.166 4.1666667
             4.167              4.166 4.1666667

6 rows selected.
MOD allows you to code to return the remainder that occurs after division. The format is MOD(the number or the column you are dividing into, the number or the column that you are dividing by). In the first example, I am dividing 17 by 5 and the remainder of 2 is shown. In the second example, I am dividing yrgoal by 12 and returning the remainder for each row.

SQL CODE:

SQL> SELECT MOD(17,5)
  2  FROM dual;

MOD(17,5)
---------
        2

SQL> SELECT yrgoal, MOD(yrgoal, 12), yrgoal/12
  2  FROM donor;

   YRGOAL MOD(YRGOAL,12) YRGOAL/12
--------- -------------- ---------
      500              8 41.666667
      400              4 33.333333

      100              4 8.3333333
       50              2 4.1666667
       50              2 4.1666667

6 rows selected.
CEIL and FLOOR are used to find the nearest whole number either greater than (CEIL) or less than (FLOOR) the decimal number being examined. In the example below I am looking for the ceiling and floor for the number 123.456.

SQL CODE:

SQL> SELECT CEIL(123.456), FLOOR(123.456)
  2  FROM dual;

CEIL(123.456) FLOOR(123.456)
------------- --------------
          124            123
POWER is used to do exponentiation. The format is POWER(number, power). In the example below I am showing yrgoal squared (power of 2) and then I am simply showing the calculation of 4 to the third power.

SQL CODE:

SQL> SELECT POWER(yrgoal,2), POWER(4,3)
  2  FROM donor;
POWER(YRGOAL,2) POWER(4,3)
--------------- ----------
         250000         64
         160000         64
                        64
          10000         64
           2500         64
           2500         64
The SIGN function returns a value that can be tested to determine if the number is 0, > 0 or < 0 (negative). You can see the values that are returned in the example below.

SQL CODE:

SQL> SELECT SIGN(0), SIGN(12), SIGN(-12)
  2  FROM dual;

  SIGN(0)  SIGN(12) SIGN(-12)
--------- --------- ---------
        0         1        -1

DATE FUNCTIONS:

Date functions allow date comparison, conversion and arithmetic. Frequently when working with dates you want to compare today's date with a date in the table. Today's date can be gotton using sysdate. In the examples below, I got the date from the system and I incorporated getting the date from the system with getting information from the donor table.

SQL CODE:

SQL> select sysdate from dual;

SYSDATE
---------
04-JUN-99

SQL> select sysdate from sys.dual;

SYSDATE
---------
04-JUN-99 

SQL> SELECT sysdate, datefst
  2  FROM donor;

SYSDATE   DATEFST
--------- ---------
05-JUN-99 03-JUL-98
05-JUN-99 24-MAY-97
05-JUN-99 03-JAN-98
05-JUN-99 04-MAR-92
05-JUN-99 04-MAR-92
05-JUN-99 04-APR-98

6 rows selected.
Date Functions Processing
ROUND Rounds date according to specifications
TRUNC Truncates date according to specifications
MONTHS_BETWEEN Returns the number of months between two dates
MONTHS_BETWEEN(date1,date2)
ADD_MONTHS Increases/decreases months -n is number of months to increase or decrease
ADD_MONTH(date,n)
NEXT_DAY Next day of the week from date specified - day is the day you of the week you are matching date to
NEXT_DAY(date,day)
LAST_DAY Returns last day of the month

The ROUND and TRUNC functions round or truncate the date. With no entry the default is day, optionally MONTH or YEAR can be used with ROUND and TRUNC. Note MONTH and YEAR should be enclosed in single quotes.

SQL CODE:

SQL> SELECT sysdate, ROUND(sysdate), TRUNC(sysdate)
  2  FROM dual;

SYSDATE   ROUND(SYS TRUNC(SYS
--------- --------- ---------
05-JUN-99 06-JUN-99 05-JUN-99

SQL> SELECT datefst, ROUND(datefst,'MONTH'), TRUNC(datefst,'MONTH')
  2  FROM donor;

DATEFST   ROUND(DAT TRUNC(DAT
--------- --------- ---------
03-JUL-98 01-JUL-98 01-JUL-98
24-MAY-97 01-JUN-97 01-MAY-97
03-JAN-98 01-JAN-98 01-JAN-98
04-MAR-92 01-MAR-92 01-MAR-92
04-MAR-92 01-MAR-92 01-MAR-92
04-APR-98 01-APR-98 01-APR-98

SQL> SELECT datefst, ROUND(datefst, 'YEAR'), TRUNC(datefst, 'YEAR')
  2  FROM donor;

DATEFST   ROUND(DAT TRUNC(DAT
--------- --------- ---------
03-JUL-98 01-JAN-99 01-JAN-98
24-MAY-97 01-JAN-97 01-JAN-97
03-JAN-98 01-JAN-98 01-JAN-98
04-MAR-92 01-JAN-92 01-JAN-92
04-MAR-92 01-JAN-92 01-JAN-92
04-APR-98 01-JAN-98 01-JAN-98
The MONTHS_BETWEEN gives the number of months between two dates. In this example, the sysdate and the datefst from the donor file are the dates used.

SQL CODE:

SQL> SELECT name, MONTHS_BETWEEN(sysdate, datefst)
  2  FROM donor;

NAME            MONTHS_BETWEEN(SYSDATE,DATEFST)
--------------- -------------------------------
Stephen Daniels                       11.090716
Jennifer Ames                         24.413297
Carl Hersey                           17.090716
Susan Ash                             87.058458
Nancy Taylor                          87.058458
Robert Brooks                         14.058458

6 rows selected.
The ADD-MONTHS function allows you to increase or decrease the months in the given date returning the new date.

SQL CODE:

SQL> SELECT name, datefst, ADD_MONTHS(datefst,3), ADD_MONTHS(datefst,-3)
  2  FROM donor;

NAME            DATEFST   ADD_MONTH ADD_MONTH
--------------- --------- --------- ---------
Stephen Daniels 03-JUL-98 03-OCT-98 03-APR-98
Jennifer Ames   24-MAY-97 24-AUG-97 24-FEB-97
Carl Hersey     03-JAN-98 03-APR-98 03-OCT-97
Susan Ash       04-MAR-92 04-JUN-92 04-DEC-91
Nancy Taylor    04-MAR-92 04-JUN-92 04-DEC-91
Robert Brooks   04-APR-98 04-JUL-98 04-JAN-98

6 rows selected.
The NEXT_DAY function returns the next time that a particular day of the week occur.

SQL CODE:

SQL> SELECT sysdate, NEXT_DAY(sysdate,'MONDAY'), datefst, NEXT_DAY(datefst,'MONDAY')
  2  FROM donor;

SYSDATE   NEXT_DAY( DATEFST   NEXT_DAY(
--------- --------- --------- ---------
05-JUN-99 07-JUN-99 03-JUL-98 06-JUL-98
05-JUN-99 07-JUN-99 24-MAY-97 26-MAY-97
05-JUN-99 07-JUN-99 03-JAN-98 05-JAN-98
05-JUN-99 07-JUN-99 04-MAR-92 09-MAR-92
05-JUN-99 07-JUN-99 04-MAR-92 09-MAR-92
05-JUN-99 07-JUN-99 04-APR-98 06-APR-98

6 rows selected.
The LAST_DAY function returns the last day of the month.

SQL CODE:

SQL> SELECT sysdate, LAST_DAY(sysdate), datefst, LAST_DAY(datefst)
  2  FROM donor;

SYSDATE   LAST_DAY( DATEFST   LAST_DAY(
--------- --------- --------- ---------
05-JUN-99 30-JUN-99 03-JUL-98 31-JUL-98
05-JUN-99 30-JUN-99 24-MAY-97 31-MAY-97
05-JUN-99 30-JUN-99 03-JAN-98 31-JAN-98
05-JUN-99 30-JUN-99 04-MAR-92 31-MAR-92
05-JUN-99 30-JUN-99 04-MAR-92 31-MAR-92
05-JUN-99 30-JUN-99 04-APR-98 30-APR-98

Conversion functions:

SQL includes functions that convert a value from one datatype to a different datatype. These functions all include a format model which allows a variety of return formats. Oracle can automatically convert VARCHAR2 to either NUMBER or DATE and can convert CHAR to NUMBER if the character string is a valid number and CHAR to DATE if the character string is in the default format of DD-MON-YY. For other conversions the following functions can be used.

Conversion function Processing
TO_CHAR A number or date can be converted to VARCHAR2 using the format specified. When converting to date, there is a fm toggle element within the fmt format that can be used to remove leading 0s or padding spaces.
TO_CHAR(data to convert [,'fmt'])
TO_CHAR(number to convert [,'fmt']
TO_NUMBER A character string containing digits is convert to number
TO_DATE A character string containing a date is converted to a date according to the format specified - default is DD-MMM-YY
The following example automatically converts a VARCHAR2 field to a number which is used in a calculation.

SQL CODE:

SQL> SELECT idno, zip, idno + zip
  2  FROM donor;

IDNO  ZIP    IDNO+ZIP
----- ----- ---------
11111 02345     13456
12121 02045     14166
22222 02045     24267
23456 02720     26176
33333 02720     36053
34567 02720     37287
When working with dates, there are a wide variety of formats that can be used. These formats also include time. The following list covers some of the formatting possibilities - check Oracle or a text for a full list.

Date Format Code Processing
Y or YY or YYY Last 1, 2 or 3 digits of the year
YYYY Year
Q Quarter (returns 1, 2, 3, 4 for the four quarters)
MM Month (returns 1 - 12)
Month Name of month
W Week of the month
DDD Day of the year
DD Day of the month
D Day of the week (1-7)
DY Three letter abbreviation of day of week
DAY Day of the week
HH or HH12 Hour using 12 hour clock
HH24 Hour using 24 hour clock
MI Minutes
SS Seconds

Examples in SQL CODE:

SQL> SELECT TO_CHAR(datefst,'YYY')
  2  FROM donor;

TO_CHAR(DATEFST,'YYY')
---------------------------------------------------------------------------
998
997
998
992
992
998

6 rows selected.

SQL> SELECT datefst, TO_CHAR(datefst,'Q')
  2  FROM donor;

DATEFST   TO_CHAR(DATEFST,'Q')
--------- -------------------------------------
03-JUL-98 3
24-MAY-97 2
03-JAN-98 1
04-MAR-92 1
04-MAR-92 1
04-APR-98 2

SQL> SELECT datefst, TO_CHAR(datefst,'DDD'), TO_CHAR(datefst,'DAY')
  2  FROM donor;

DATEFST   TO_CHAR(DATEFST,'DDD')
--------- ----------------------------------------------------------
TO_CHAR(DATEFST,'DAY')
--------------------------------------------------------------------
03-JUL-98 184
FRIDAY

24-MAY-97 144
SATURDAY

03-JAN-98 003
SATURDAY

04-MAR-92 064
WEDNESDAY

04-MAR-92 064
WEDNESDAY

04-APR-98 094
SATURDAY

SQL> SELECT TO_CHAR(datefst, 'fmDD Month YYYY'), datefst
  2  FROM donor;

TO_CHAR(DATEFST,'FMDDMONTHYYYY')                                            DATEFST
--------------------------------------------------------------------------- ---------
3 July 1998                                                                 03-JUL-98
24 May 1997                                                                 24-MAY-97
3 January 1998                                                              03-JAN-98
4 March 1992                                                                04-MAR-92
4 March 1992                                                                04-MAR-92
4 April 1998                                                                04-APR-98

6 rows selected.
There are a wide variety of numeric formatting possibilities that can be used to display numeric numbers. They are used with the TO-CHAR conversion to a VARCHAR2 field. These formatting options allow the insertion of editing characters such as commas, dollar signs etc. Some of the formats are shown below. For a complete reference see Oracle help or a text book. The format is TO_CHAR(number to be formatted, 'fmt').

Numeric Format Processing
9 Numeric postions (determines width of display)
0 Used to display leading zeros (example: 09999)
$ Floating dollar sign
. Decimal point (insert in display where needed)
, Comma (insert in display where needed)
MI Minus sign for negative numbers (displays on right)
PR Negative numbers in parenthesis
B Display zeros as blank

Examples of numeric formatting in SQL CODE:

1  SELECT TO_CHAR(yrgoal,'$9,999.99'), TO_CHAR(yrgoal*-1,'9999PR')
2* FROM donor
SQL> /

TO_CHAR(YR TO_CHA
---------- ------
   $500.00  <500>
   $400.00  <400>

   $100.00  <100>
    $50.00   <50>
    $50.00   <50>

1  SELECT TO_CHAR(yrgoal*100,'$99,999.99'), TO_CHAR(yrgoal*-1,'9999MI')
2* FROM donor
SQL> /

TO_CHAR(YRG TO_CH
----------- -----
 $50,000.00  500-
 $40,000.00  400-

 $10,000.00  100-
  $5,000.00   50-
  $5,000.00   50-

SQL> SELECT TO_CHAR(yrgoal,'0999'), TO_CHAR(yrgoal,'B999.99')
  2  FROM donor;

TO_CH TO_CHAR
----- -------
 0500  500.00
 0400  400.00

 0100  100.00
 0050   50.00
 0050   50.00

6 rows selected.
The NVL function converts null values to actual values. The format is:
NVL(the source that may contain null, the value to be used)

SQL CODE:

SQL> SELECT yrgoal, NVL(yrgoal,0)
  2  FROM donor;

   YRGOAL NVL(YRGOAL,0)
--------- -------------
      500           500
      400           400
                      0
      100           100
       50            50
50 50

SQL> SELECT yrgoal, NVL(yrgoal, 10)
  2  FROM donor;

   YRGOAL NVL(YRGOAL,10)
--------- --------------
      500            500
      400            400
                      10
      100            100
       50             50
       50             50