 Chapter 4 
Functions: Molding the Data You Retrieve
Objectives
ToChapter we talk about functions. Functions in SQL enable you to perform feats such
as determining the sum of a column or converting all the characters of a string to
uppercase. By the end of the day, you will understand and be able to use all the
following:
 Aggregate functions
 Date and time functions
 Arithmetic functions
 Character functions
 Conversion functions
 Miscellaneous functions
These functions greatly increase your ability to manipulate the information you
retrieved using the basic functions of SQL that were described earlier this week.
The first five aggregate functions, COUNT, SUM, AVG, MAX,
and MIN, are defined in the ANSI standard. Most implementations of SQL have
extensions to these aggregate functions, some of which are covered today. Some implementations
may use different names for these functions.
Aggregate Functions
These functions are also referred to as group functions. They return a value based
on the values in a column. (After all, you wouldn't ask for the average of a single
field.) The examples in this section use the table TEAMSTATS:
INPUT:
SQL> SELECT * FROM TEAMSTATS;
OUTPUT:
NAME POS AB HITS WALKS SINGLES DOUBLES TRIPLES HR SO
         
JONES 1B 145 45 34 31 8 1 5 10
DONKNOW 3B 175 65 23 50 10 1 4 15
WORLEY LF 157 49 15 35 8 3 3 16
DAVID OF 187 70 24 48 4 0 17 42
HAMHOCKER 3B 50 12 10 10 2 0 0 13
CASEY DH 1 0 0 0 0 0 0 1
6 rows selected.
COUNT
The function COUNT returns the number of rows that satisfy the condition
in the WHERE clause. Say you wanted to know how many ball players were hitting
under 350. You would type
INPUT/OUTPUT:
SQL> SELECT COUNT(*)
2 FROM TEAMSTATS
3 WHERE HITS/AB < .35;
COUNT(*)

4
To make the code more readable, try an alias:
INPUT/OUTPUT:
SQL> SELECT COUNT(*) NUM_BELOW_350
2 FROM TEAMSTATS
3 WHERE HITS/AB < .35;
NUM_BELOW_350

4
Would it make any difference if you tried a column name instead of the asterisk?
(Notice the use of parentheses around the column names.) Try this:
INPUT/OUTPUT:
SQL> SELECT COUNT(NAME) NUM_BELOW_350
2 FROM TEAMSTATS
3 WHERE HITS/AB < .35;
NUM_BELOW_350

4
The answer is no. The NAME column that you selected was not involved
in the WHERE statement. If you use COUNT without a WHERE
clause, it returns the number of records in the table.
INPUT/OUTPUT:
SQL> SELECT COUNT(*)
2 FROM TEAMSTATS;
COUNT(*)

6
SUM
SUM does just that. It returns the sum of all values in a column. To
find out how many singles have been hit, type
INPUT:
SQL> SELECT SUM(SINGLES) TOTAL_SINGLES
2 FROM TEAMSTATS;
OUTPUT:
TOTAL_SINGLES

174
To get several sums, use
INPUT/OUTPUT:
SQL> SELECT SUM(SINGLES) TOTAL_SINGLES, SUM(DOUBLES) TOTAL_DOUBLES,
SUM(TRIPLES) TOTAL_TRIPLES, SUM(HR) TOTAL_HR
2 FROM TEAMSTATS;
TOTAL_SINGLES TOTAL_DOUBLES TOTAL_TRIPLES TOTAL_HR
   
174 32 5 29
To collect similar information on all 300 or better players, type
INPUT/OUTPUT:
SQL> SELECT SUM(SINGLES) TOTAL_SINGLES, SUM(DOUBLES) TOTAL_DOUBLES,
SUM(TRIPLES) TOTAL_TRIPLES, SUM(HR) TOTAL_HR
2 FROM TEAMSTATS
3 WHERE HITS/AB >= .300;
TOTAL_SINGLES TOTAL_DOUBLES TOTAL_TRIPLES TOTAL_HR
   
164 30 5 29
To compute a team batting average, type
INPUT/OUTPUT:
SQL> SELECT SUM(HITS)/SUM(AB) TEAM_AVERAGE
2 FROM TEAMSTATS;
TEAM_AVERAGE

.33706294
SUM works only with numbers. If you try it on a nonnumerical field, you
get
INPUT/OUTPUT:
SQL> SELECT SUM(NAME)
2 FROM TEAMSTATS;
ERROR:
ORA01722: invalid number
no rows selected
This error message is logical because you cannot sum a group of names.
AVG
The AVG function computes the average of a column. To find the average
number of strike outs, use this:
INPUT:
SQL> SELECT AVG(SO) AVE_STRIKE_OUTS
2 FROM TEAMSTATS;
OUTPUT:
AVE_STRIKE_OUTS

16.166667
The following example illustrates the difference between SUM and AVG:
INPUT/OUTPUT:
SQL> SELECT AVG(HITS/AB) TEAM_AVERAGE
2 FROM TEAMSTATS;
TEAM_AVERAGE

.26803448
ANALYSIS:
The team was batting over 300 in the previous example! What happened? AVG
computed the average of the combined column hits divided by at bats, whereas the
example with SUM divided the total number of hits by the number of at bats.
For example, player A gets 50 hits in 100 at bats for a .500 average. Player B gets
0 hits in 1 at bat for a 0.0 average. The average of 0.0 and 0.5 is .250. If you
compute the combined average of 50 hits in 101 at bats, the answer is a respectable
.495. The following statement returns the correct batting average:
INPUT/OUTPUT:
SQL> SELECT AVG(HITS)/AVG(AB) TEAM_AVERAGE
2 FROM TEAMSTATS;
TEAM_AVERAGE

.33706294
Like the SUM function, AVG works only with numbers.
MAX
If you want to find the largest value in a column, use MAX. For example,
what is the highest number of hits?
INPUT:
SQL> SELECT MAX(HITS)
2 FROM TEAMSTATS;
OUTPUT:
MAX(HITS)

70
Can you find out who has the most hits?
INPUT/OUTPUT:
SQL> SELECT NAME
2 FROM TEAMSTATS
3 WHERE HITS = MAX(HITS);
ERROR at line 3:
ORA00934: group function is not allowed here
Unfortunately, you can't. The error message is a reminder that this group function
(remember that aggregate functions are also called group functions)
does not work in the WHERE clause. Don't despair, Chapter 7, "Subqueries:
The Embedded SELECT Statement," covers the concept of subqueries and
explains a way to find who has the MAX hits.
What happens if you try a nonnumerical column?
INPUT/OUTPUT:
SQL> SELECT MAX(NAME)
2 FROM TEAMSTATS;
MAX(NAME)

WORLEY
Here's something new. MAX returns the highest (closest to Z) string.
Finally, a function that works with both characters and numbers.
MIN
MIN does the expected thing and works like MAX except it returns
the lowest member of a column. To find out the fewest at bats, type
INPUT:
SQL> SELECT MIN(AB)
2 FROM TEAMSTATS;
OUTPUT:
MIN(AB)

1
The following statement returns the name closest to the beginning of the alphabet:
INPUT/OUTPUT:
SQL> SELECT MIN(NAME)
2 FROM TEAMSTATS;
MIN(NAME)

CASEY
You can combine MIN with MAX to give a range of values. For
example:
INPUT/OUTPUT:
SQL> SELECT MIN(AB), MAX(AB)
2 FROM TEAMSTATS;
MIN(AB) MAX(AB)
 
1 187
This sort of information can be useful when using statistical functions.
NOTE: As we mentioned in the introduction,
the first five aggregate functions are described in the ANSI standard. The remaining
aggregate functions have become de facto standards, present in all important implementations
of SQL. We use the Oracle7 names for these functions. Other implementations may use
different names.
VARIANCE
VARIANCE produces the square of the standard deviation, a number vital
to many statistical calculations. It works like this:
INPUT:
SQL> SELECT VARIANCE(HITS)
2 FROM TEAMSTATS;
OUTPUT:
VARIANCE(HITS)

802.96667
If you try a string
INPUT/OUTPUT:
SQL> SELECT VARIANCE(NAME)
2 FROM TEAMSTATS;
ERROR:
ORA01722: invalid number
no rows selected
you find that VARIANCE is another function that works exclusively with
numbers.
STDDEV
The final group function, STDDEV, finds the standard deviation of a column
of numbers, as demonstrated by this example:
INPUT:
SQL> SELECT STDDEV(HITS)
2 FROM TEAMSTATS;
OUTPUT:
STDDEV(HITS)

28.336666
It also returns an error when confronted by a string:
INPUT/OUTPUT:
SQL> SELECT STDDEV(NAME)
2 FROM TEAMSTATS;
ERROR:
ORA01722: invalid number
no rows selected
These aggregate functions can also be used in various combinations:
INPUT/OUTPUT:
SQL> SELECT COUNT(AB),
2 AVG(AB),
3 MIN(AB),
4 MAX(AB),
5 STDDEV(AB),
6 VARIANCE(AB),
7 SUM(AB)
8 FROM TEAMSTATS;
COUNT(AB) AVG(AB) MIN(AB) MAX(AB) STDDEV(AB) VARIANCE(AB) SUM(AB)
      
6 119.167 1 187 75.589 5712.97 715
The next time you hear a sportscaster use statistics to fill the time between
plays, you will know that SQL is at work somewhere behind the scenes.
Date and Time Functions
We live in a civilization governed by times and dates, and most major implementations
of SQL have functions to cope with these concepts. This section uses the table PROJECT
to demonstrate the time and date functions.
INPUT:
SQL> SELECT * FROM PROJECT;
OUTPUT:
TASK STARTDATE ENDDATE
  
KICKOFF MTG 01APR95 01APR95
TECH SURVEY 02APR95 01MAY95
USER MTGS 15MAY95 30MAY95
DESIGN WIDGET 01JUN95 30JUN95
CODE WIDGET 01JUL95 02SEP95
TESTING 03SEP95 17JAN96
6 rows selected.
NOTE: This table used the Date data type.
Most implementations of SQL have a Date data type, but the exact syntax may vary.
ADD_MONTHS
This function adds a number of months to a specified date. For example, say something
extraordinary happened, and the preceding project slipped to the right by two months.
You could make a new schedule by typing
INPUT:
SQL> SELECT TASK,
2 STARTDATE,
3 ENDDATE ORIGINAL_END,
4 ADD_MONTHS(ENDDATE,2)
5 FROM PROJECT;
OUTPUT:
TASK STARTDATE ORIGINAL_ ADD_MONTH
   
KICKOFF MTG 01APR95 01APR95 01JUN95
TECH SURVEY 02APR95 01MAY95 01JUL95
USER MTGS 15MAY95 30MAY95 30JUL95
DESIGN WIDGET 01JUN95 30JUN95 31AUG95
CODE WIDGET 01JUL95 02SEP95 02NOV95
TESTING 03SEP95 17JAN96 17MAR96
6 rows selected.
Not that a slip like this is possible, but it's nice to have a function that makes
it so easy. ADD_MONTHS also works outside the SELECT clause. Typing
INPUT:
SQL> SELECT TASK TASKS_SHORTER_THAN_ONE_MONTH
2 FROM PROJECT
3 WHERE ADD_MONTHS(STARTDATE,1) > ENDDATE;
produces the following result:
OUTPUT:
TASKS_SHORTER_THAN_ONE_MONTH

KICKOFF MTG
TECH SURVEY
USER MTGS
DESIGN WIDGET
ANALYSIS:
You will find that all the functions in this section work in more than one place.
However, ADD MONTHS does not work with other data types like character or
number without the help of functions TO_CHAR and TO_DATE, which
are discussed later today.
LAST_DAY
LAST_DAY returns the last Chapter of a specified month. It is for those of
us who haven't mastered the "Thirty days has September..." rhymeor at
least those of us who have not yet taught it to our computers. If, for example, you
need to know what the last Chapter of the month is in the column ENDDATE, you
would type
INPUT:
SQL> SELECT ENDDATE, LAST_DAY(ENDDATE)
2 FROM PROJECT;
Here's the result:
OUTPUT:
ENDDATE LAST_DAY(ENDDATE)
 
01APR95 30APR95
01MAY95 31MAY95
30MAY95 31MAY95
30JUN95 30JUN95
02SEP95 30SEP95
17JAN96 31JAN96
6 rows selected.
How does LAST DAY handle leap years?
INPUT/OUTPUT:
SQL> SELECT LAST_DAY('1FEB95') NON_LEAP,
2 LAST_DAY('1FEB96') LEAP
3 FROM PROJECT;
NON_LEAP LEAP
 
28FEB95 29FEB96
28FEB95 29FEB96
28FEB95 29FEB96
28FEB95 29FEB96
28FEB95 29FEB96
28FEB95 29FEB96
6 rows selected.
ANALYSIS:
You got the right result, but why were so many rows returned? Because you didn't
specify an existing column or any conditions, the SQL engine applied the date functions
in the statement to each existing row. Let's get something less redundant by using
the following:
INPUT:
SQL> SELECT DISTINCT LAST_DAY('1FEB95') NON_LEAP,
2 LAST_DAY('1FEB96') LEAP
3 FROM PROJECT;
This statement uses the word DISTINCT (see Chapter 2, "Introduction
to the Query: The SELECT Statement") to produce the singular result
OUTPUT:
NON_LEAP LEAP
 
28FEB95 29FEB96
Unlike me, this function knows which years are leap years. But before you trust
your own or your company's financial future to this or any other function, check
your implementation!
MONTHS_BETWEEN
If you need to know how many months fall between month x and month y, use MONTHS_BETWEEN
like this:
INPUT:
SQL> SELECT TASK, STARTDATE, ENDDATE,MONTHS_BETWEEN(STARTDATE,ENDDATE)
DURATION
2 FROM PROJECT;
OUTPUT:
TASK STARTDATE ENDDATE DURATION
   
KICKOFF MTG 01APR95 01APR95 0
TECH SURVEY 02APR95 01MAY95 .9677419
USER MTGS 15MAY95 30MAY95 .483871
DESIGN WIDGET 01JUN95 30JUN95 .9354839
CODE WIDGET 01JUL95 02SEP95 2.032258
TESTING 03SEP95 17JAN96 4.451613
6 rows selected.
Wait a minutethat doesn't look right. Try this:
INPUT/OUTPUT:
SQL> SELECT TASK, STARTDATE, ENDDATE,
2 MONTHS_BETWEEN(ENDDATE,STARTDATE) DURATION
3 FROM PROJECT;
TASK STARTDATE ENDDATE DURATION
   
KICKOFF MTG 01APR95 01APR95 0
TECH SURVEY 02APR95 01MAY95 .96774194
USER MTGS 15MAY95 30MAY95 .48387097
DESIGN WIDGET 01JUN95 30JUN95 .93548387
CODE WIDGET 01JUL95 02SEP95 2.0322581
TESTING 03SEP95 17JAN96 4.4516129
6 rows selected.
ANALYSIS:
That's better. You see that MONTHS_BETWEEN is sensitive to the way you
order the months. Negative months might not be bad. For example, you could use a
negative result to determine whether one date happened before another. For example,
the following statement shows all the tasks that started before May 19, 1995:
INPUT:
SQL> SELECT *
2 FROM PROJECT
3 WHERE MONTHS_BETWEEN('19 MAY 95', STARTDATE) > 0;
OUTPUT:
TASK STARTDATE ENDDATE
  
KICKOFF MTG 01APR95 01APR95
TECH SURVEY 02APR95 01MAY95
USER MTGS 15MAY95 30MAY95
NEW_TIME
If you need to adjust the time according to the time zone you are in, the New_TIME
function is for you. Here are the time zones you can use with this function:
Abbreviation 
Time Zone 
AST or ADT 
Atlantic standard or daylight time 
BST or BDT 
Bering standard or daylight time 
CST or CDT 
Central standard or daylight time 
EST or EDT 
Eastern standard or daylight time 
GMT 
Greenwich mean time 
HST or HDT 
AlaskaHawaii standard or daylight time 
MST or MDT 
Mountain standard or daylight time 
NST 
Newfoundland standard time 
PST or PDT 
Pacific standard or daylight time 
YST or YDT 
Yukon standard or daylight time 
You can adjust your time like this:
INPUT:
SQL> SELECT ENDDATE EDT,
2 NEW_TIME(ENDDATE, 'EDT','PDT')
3 FROM PROJECT;
OUTPUT:
EDT NEW_TIME(ENDDATE
 
01APR95 1200AM 31MAR95 0900PM
01MAY95 1200AM 30APR95 0900PM
30MAY95 1200AM 29MAY95 0900PM
30JUN95 1200AM 29JUN95 0900PM
02SEP95 1200AM 01SEP95 0900PM
17JAN96 1200AM 16JAN96 0900PM
6 rows selected.
Like magic, all the times are in the new time zone and the dates are adjusted.
NEXT_DAY
NEXT_DAY finds the name of the first Chapter of the week that is equal to
or later than another specified date. For example, to send a report on the Friday
following the first Chapter of each event, you would type
INPUT:
SQL> SELECT STARTDATE,
2 NEXT_DAY(STARTDATE, 'FRIDAY')
3 FROM PROJECT;
which would return
OUTPUT:
STARTDATE NEXT_DAY(
 
01APR95 07APR95
02APR95 07APR95
15MAY95 19MAY95
01JUN95 02JUN95
01JUL95 07JUL95
03SEP95 08SEP95
6 rows selected.
ANALYSIS:
The output tells you the date of the first FriChapter that occurs after your STARTDATE.
SYSDATE
SYSDATE returns the system time and date:
INPUT:
SQL> SELECT DISTINCT SYSDATE
2 FROM PROJECT;
OUTPUT:
SYSDATE

18JUN95 1020PM
If you wanted to see where you stood toChapter in a certain project, you could type
INPUT/OUTPUT:
SQL> SELECT *
2 FROM PROJECT
3 WHERE STARTDATE > SYSDATE;
TASK STARTDATE ENDDATE
  
CODE WIDGET 01JUL95 02SEP95
TESTING 03SEP95 17JAN96
Now you can see what parts of the project start after today.
Arithmetic Functions
Many of the uses you have for the data you retrieve involve mathematics. Most
implementations of SQL provide arithmetic functions similar to the functions covered
here. The examples in this section use the NUMBERS table:
INPUT:
SQL> SELECT *
2 FROM NUMBERS;
OUTPUT:
A B
 
3.1415 4
45 .707
5 9
57.667 42
15 55
7.2 5.3
6 rows selected.
ABS
The ABS function returns the absolute value of the number you point to.
For example:
INPUT:
SQL> SELECT ABS(A) ABSOLUTE_VALUE
2 FROM NUMBERS;
OUTPUT:
ABSOLUTE_VALUE

3.1415
45
5
57.667
15
7.2
6 rows selected.
ABS changes all the negative numbers to positive and leaves positive
numbers alone.
CEIL and FLOOR
CEIL returns the smallest integer greater than or equal to its argument.
FLOOR does just the reverse, returning the largest integer equal to or less
than its argument. For example:
INPUT:
SQL> SELECT B, CEIL(B) CEILING
2 FROM NUMBERS;
OUTPUT:
B CEILING
 
4 4
.707 1
9 9
42 42
55 55
5.3 6
6 rows selected.
And
INPUT/OUTPUT:
SQL> SELECT A, FLOOR(A) FLOOR
2 FROM NUMBERS;
A FLOOR
 
3.1415 3
45 45
5 5
57.667 58
15 15
7.2 8
6 rows selected.
COS, COSH, SIN, SINH, TAN, and TANH
The COS, SIN, and TAN functions provide support for
various trigonometric concepts. They all work on the assumption that n is in radians.
The following statement returns some unexpected values if you don't realize COS
expects A to be in radians.
INPUT:
SQL> SELECT A, COS(A)
2 FROM NUMBERS;
OUTPUT:
A COS(A)
 
3.1415 1
45 .52532199
5 .28366219
57.667 .437183
15 .7596879
7.2 .60835131
ANALYSIS:
You would expect the COS of 45 degrees to be in the neighborhood
of .707, not .525. To make this function work the way you would
expect it to in a degreeoriented world, you need to convert degrees to radians.
(When was the last time you heard a news broadcast report that a politician had done
a piradian turn? You hear about a 180degree turn.) Because 360 degrees = 2 pi radians,
you can write
INPUT/OUTPUT:
SQL> SELECT A, COS(A* 0.01745329251994)
2 FROM NUMBERS;
A COS(A*0.01745329251994)
 
3.1415 .99849724
45 .70710678
5 .9961947
57.667 .5348391
15 .96592583
7.2 .9921147
ANALYSIS:
Note that the number 0.01745329251994 is radians divided by degrees.
The trigonometric functions work as follows:
INPUT/OUTPUT:
SQL> SELECT A, COS(A*0.017453), COSH(A*0.017453)
2 FROM NUMBERS;
A COS(A*0.017453) COSH(A*0.017453)
  
3.1415 .99849729 1.0015035
45 .70711609 1.3245977
5 .99619483 1.00381
57.667 .53485335 1.5507072
15 .96592696 1.0344645
7.2 .99211497 1.0079058
6 rows selected.
And
INPUT/OUTPUT:
SQL> SELECT A, SIN(A*0.017453), SINH(A*0.017453)
2 FROM NUMBERS;
A SIN(A*0.017453) SINH(A*0.017453)
  
3.1415 .05480113 .05485607
45 .7070975 .8686535
5 .08715429 .0873758
57.667 .8449449 1.185197
15 .25881481 .26479569
7.2 .1253311 .1259926
6 rows selected.
And
INPUT/OUTPUT:
SQL> SELECT A, TAN(A*0.017453), TANH(A*0.017453)
2 FROM NUMBERS;
A TAN(A*0.017453) TANH(A*0.017453)
  
3.1415 .05488361 .05477372
45 .9999737 .6557867
5 .08748719 .08704416
57.667 1.579769 .7642948
15 .26794449 .25597369
7.2 .1263272 .1250043
6 rows selected.
EXP
EXP enables you to raise e (e is a mathematical constant
used in various formulas) to a power. Here's how EXP raises e by
the values in column A:
INPUT:
SQL> SELECT A, EXP(A)
2 FROM NUMBERS;
OUTPUT:
A EXP(A)
 
3.1415 23.138549
45 2.863E20
5 148.41316
57.667 9.027E26
15 3269017.4
7.2 .00074659
6 rows selected.
LN and LOG
These two functions center on logarithms. LN returns the natural logarithm
of its argument. For example:
INPUT:
SQL> SELECT A, LN(A)
2 FROM NUMBERS;
OUTPUT:
ERROR:
ORA01428: argument '45' is out of range
Did we neglect to mention that the argument had to be positive? Write
INPUT/OUTPUT:
SQL> SELECT A, LN(ABS(A))
2 FROM NUMBERS;
A LN(ABS(A))
 
3.1415 1.1447004
45 3.8066625
5 1.6094379
57.667 4.0546851
15 2.7080502
7.2 1.974081
6 rows selected.
ANALYSIS:
Notice how you can embed the function ABS inside the LN call.
The other logarithmic function, LOG, takes two arguments, returning the
logarithm of the first argument in the base of the second. The following query returns
the logarithms of column B in base 10.
INPUT/OUTPUT:
SQL> SELECT B, LOG(B, 10)
2 FROM NUMBERS;
B LOG(B,10)
 
4 1.660964
.707 6.640962
9 1.0479516
42 .61604832
55 .57459287
5.3 1.3806894
6 rows selected.
MOD
You have encountered MOD before. On Chapter 3, "Expressions, Conditions,
and Operators," you saw that the ANSI standard for the modulo operator %
is sometimes implemented as the function MOD. Here's a query that returns
a table showing the remainder of A divided by B:
INPUT:
SQL> SELECT A, B, MOD(A,B)
2 FROM NUMBERS;
OUTPUT:
A B MOD(A,B)
  
3.1415 4 3.1415
45 .707 .459
5 9 5
57.667 42 15.667
15 55 15
7.2 5.3 1.9
6 rows selected.
POWER
To raise one number to the power of another, use POWER. In this function
the first argument is raised to the power of the second:
INPUT:
SQL> SELECT A, B, POWER(A,B)
2 FROM NUMBERS;
OUTPUT:
ERROR:
ORA01428: argument '45' is out of range
ANALYSIS:
At first glance you are likely to think that the first argument can't be negative.
But that impression can't be true, because a number like 4 can be raised to a power.
Therefore, if the first number in the POWER function is negative, the second
must be an integer. You can work around this problem by using CEIL (or FLOOR):
INPUT:
SQL> SELECT A, CEIL(B), POWER(A,CEIL(B))
2 FROM NUMBERS;
OUTPUT:
A CEIL(B) POWER(A,CEIL(B))
  
3.1415 4 97.3976
45 1 45
5 9 1953125
57.667 42 9.098E+73
15 55 4.842E+64
7.2 6 139314.07
6 rows selected.
That's better!
SIGN
SIGN returns 1 if its argument is less than 0, 0
if its argument is equal to 0, and 1 if its argument is greater
than 0, as shown in the following example:
INPUT:
SQL> SELECT A, SIGN(A)
2 FROM NUMBERS;
OUTPUT:
A SIGN(A)
 
3.1415 1
45 1
5 1
57.667 1
15 1
7.2 1
0 0
7 rows selected.
You could also use SIGN in a SELECT WHERE clause like this:
INPUT:
SQL> SELECT A
2 FROM NUMBERS
3 WHERE SIGN(A) = 1;
OUTPUT:
A

3.1415
5
15
SQRT
The function SQRT returns the square root of an argument. Because the
square root of a negative number is undefined, you cannot use SQRT on negative
numbers.
INPUT/OUTPUT:
SQL> SELECT A, SQRT(A)
2 FROM NUMBERS;
ERROR:
ORA01428: argument '45' is out of range
However, you can fix this limitation with ABS:
INPUT/OUTPUT:
SQL> SELECT ABS(A), SQRT(ABS(A))
2 FROM NUMBERS;
ABS(A) SQRT(ABS(A))
 
3.1415 1.7724277
45 6.7082039
5 2.236068
57.667 7.5938791
15 3.8729833
7.2 2.6832816
0 0
7 rows selected.
Character Functions
Many implementations of SQL provide functions to manipulate characters and strings
of characters. This section covers the most common character functions. The examples
in this section use the table CHARACTERS.
INPUT/OUTPUT:
SQL> SELECT * FROM CHARACTERS;
LASTNAME FIRSTNAME M CODE
   
PURVIS KELLY A 32
TAYLOR CHUCK J 67
CHRISTINE LAURA C 65
ADAMS FESTER M 87
COSTALES ARMANDO A 77
KONG MAJOR G 52
6 rows selected.
CHR
CHR returns the character equivalent of the number it uses as an argument.
The character it returns depends on the character set of the database. For this example
the database is set to ASCII. The column CODE includes numbers.
INPUT:
SQL> SELECT CODE, CHR(CODE)
2 FROM CHARACTERS;
OUTPUT:
CODE CH
 
32
67 C
65 A
87 W
77 M
52 4
6 rows selected.
The space opposite the 32 shows that 32 is a space in the ASCII
character set.
CONCAT
You used the equivalent of this function on Chapter 3, when you learned about operators.
The  symbol splices two strings together, as does CONCAT. It
works like this:
INPUT:
SQL> SELECT CONCAT(FIRSTNAME, LASTNAME) "FIRST AND LAST NAMES"
2 FROM CHARACTERS;
OUTPUT:
FIRST AND LAST NAMES

KELLY PURVIS
CHUCK TAYLOR
LAURA CHRISTINE
FESTER ADAMS
ARMANDO COSTALES
MAJOR KONG
6 rows selected.
ANALYSIS:
Quotation marks surround the multipleword alias FIRST AND LAST NAMES.
Again, it is safest to check your implementation to see if it allows multipleword
aliases.
Also notice that even though the table looks like two separate columns, what you
are seeing is one column. The first value you concatenated, FIRSTNAME, is
15 characters wide. This operation retained all the characters in the field.
INITCAP
INITCAP capitalizes the first letter of a word and makes all other characters
lowercase.
INPUT:
SQL> SELECT FIRSTNAME BEFORE, INITCAP(FIRSTNAME) AFTER
2 FROM CHARACTERS;
OUTPUT:
BEFORE AFTER
 
KELLY Kelly
CHUCK Chuck
LAURA Laura
FESTER Fester
ARMANDO Armando
MAJOR Major
6 rows selected.
LOWER and UPPER
As you might expect, LOWER changes all the characters to lowercase; UPPER
does just the reverse.
The following example starts by doing a little magic with the UPDATE
function (you learn more about this next week) to change one of the values to lowercase:
INPUT:
SQL> UPDATE CHARACTERS
2 SET FIRSTNAME = 'kelly'
3 WHERE FIRSTNAME = 'KELLY';
OUTPUT:
1 row updated.
INPUT:
SQL> SELECT FIRSTNAME
2 FROM CHARACTERS;
OUTPUT:
FIRSTNAME

kelly
CHUCK
LAURA
FESTER
ARMANDO
MAJOR
6 rows selected.
Then you write
INPUT:
SQL> SELECT FIRSTNAME, UPPER(FIRSTNAME), LOWER(FIRSTNAME)
2 FROM CHARACTERS;
OUTPUT:
FIRSTNAME UPPER(FIRSTNAME LOWER(FIRSTNAME
  
kelly KELLY kelly
CHUCK CHUCK chuck
LAURA LAURA laura
FESTER FESTER fester
ARMANDO ARMANDO armando
MAJOR MAJOR major
6 rows selected.
Now you see the desired behavior.
LPAD and
RPAD
LPAD and RPAD take a minimum of two and a maximum of three arguments.
The first argument is the character string to be operated on. The second is the number
of characters to pad it with, and the optional third argument is the character to
pad it with. The third argument defaults to a blank, or it can be a single character
or a character string. The following statement adds five pad characters, assuming
that the field LASTNAME is defined as a 15character field:
INPUT:
SQL> SELECT LASTNAME, LPAD(LASTNAME,20,'*')
2 FROM CHARACTERS;
OUTPUT:
LASTNAME LPAD(LASTNAME,20,'*'
 
PURVIS *****PURVIS
TAYLOR *****TAYLOR
CHRISTINE *****CHRISTINE
ADAMS *****ADAMS
COSTALES *****COSTALES
KONG *****KONG
6 rows selected.
ANALYSIS:
Why were only five pad characters added? Remember that the LASTNAME column
is 15 characters wide and that LASTNAME includes the blanks to the right
of the characters that make up the name. Some column data types eliminate padding
characters if the width of the column value is less than the total width allocated
for the column. Check your implementation. Now try the right side:
INPUT:
SQL> SELECT LASTNAME, RPAD(LASTNAME,20,'*')
2 FROM CHARACTERS;
OUTPUT:
LASTNAME RPAD(LASTNAME,20,'*'
 
PURVIS PURVIS *****
TAYLOR TAYLOR *****
CHRISTINE CHRISTINE *****
ADAMS ADAMS *****
COSTALES COSTALES *****
KONG KONG *****
6 rows selected.
ANALYSIS:
Here you see that the blanks are considered part of the field name for these operations.
The next two functions come in handy in this type of situation.
LTRIM and
RTRIM
LTRIM and RTRIM take at least one and at most two arguments.
The first argument, like LPAD and RPAD, is a character string.
The optional second element is either a character or character string or defaults
to a blank. If you use a second argument that is not a blank, these trim functions
will trim that character the same way they trim the blanks in the following examples.
INPUT:
SQL> SELECT LASTNAME, RTRIM(LASTNAME)
2 FROM CHARACTERS;
OUTPUT:
LASTNAME RTRIM(LASTNAME)
 
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRISTINE
ADAMS ADAMS
COSTALES COSTALES
KONG KONG
6 rows selected.
You can make sure that the characters have been trimmed with the following statement:
INPUT:
SQL> SELECT LASTNAME, RPAD(RTRIM(LASTNAME),20,'*')
2 FROM CHARACTERS;
OUTPUT:
LASTNAME RPAD(RTRIM(LASTNAME)
 
PURVIS PURVIS**************
TAYLOR TAYLOR**************
CHRISTINE CHRISTINE***********
ADAMS ADAMS***************
COSTALES COSTALES************
KONG KONG****************
6 rows selected.
The output proves that trim is working. Now try LTRIM:
INPUT:
SQL> SELECT LASTNAME, LTRIM(LASTNAME, 'C')
2 FROM CHARACTERS;
OUTPUT:
LASTNAME LTRIM(LASTNAME,
 
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE HRISTINE
ADAMS ADAMS
COSTALES OSTALES
KONG KONG
6 rows selected.
Note the missing Cs in the third and fifth rows.
REPLACE
REPLACE does just that. Of its three arguments, the first is the string
to be searched. The second is the search key. The last is the optional replacement
string. If the third argument is left out or NULL, each occurrence of the
search key on the string to be searched is removed and is not replaced with anything.
INPUT:
SQL> SELECT LASTNAME, REPLACE(LASTNAME, 'ST') REPLACEMENT
2 FROM CHARACTERS;
OUTPUT:
LASTNAME REPLACEMENT
 
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRIINE
ADAMS ADAMS
COSTALES COALES
KONG KONG
6 rows selected.
If you have a third argument, it is substituted for each occurrence of the search
key in the target string. For example:
INPUT:
SQL> SELECT LASTNAME, REPLACE(LASTNAME, 'ST','**') REPLACEMENT
2 FROM CHARACTERS;
OUTPUT:
LASTNAME REPLACEMENT
 
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRI**INE
ADAMS ADAMS
COSTALES CO**ALES
KONG KONG
6 rows selected.
If the second argument is NULL, the target string is returned with no
changes.
INPUT:
SQL> SELECT LASTNAME, REPLACE(LASTNAME, NULL) REPLACEMENT
2 FROM CHARACTERS;
OUTPUT:
LASTNAME REPLACEMENT
 
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRISTINE
ADAMS ADAMS
COSTALES COSTALES
KONG KONG
6 rows selected.
SUBSTR
This threeargument function enables you to take a piece out of a target string.
The first argument is the target string. The second argument is the position of the
first character to be output. The third argument is the number of characters to show.
INPUT:
SQL> SELECT FIRSTNAME, SUBSTR(FIRSTNAME,2,3)
2 FROM CHARACTERS;
OUTPUT:
FIRSTNAME SUB
 
kelly ell
CHUCK HUC
LAURA AUR
FESTER EST
ARMANDO RMA
MAJOR AJO
6 rows selected.
If you use a negative number as the second argument, the starting point is determined
by counting backwards from the end, like this:
INPUT:
SQL> SELECT FIRSTNAME, SUBSTR(FIRSTNAME,13,2)
2 FROM CHARACTERS;
OUTPUT:
FIRSTNAME SU
 
kelly ll
CHUCK UC
LAURA UR
FESTER ST
ARMANDO MA
MAJOR JO
6 rows selected.
ANALYSIS:
Remember the character field FIRSTNAME in this example is 15 characters
long. That is why you used a 13 to start at the third character. Counting
back from 15 puts you at the start of the third character, not at the start of the
second. If you don't have a third argument, use the following statement instead:
INPUT:
SQL> SELECT FIRSTNAME, SUBSTR(FIRSTNAME,3)
2 FROM CHARACTERS;
OUTPUT:
FIRSTNAME SUBSTR(FIRSTN
 
kelly lly
CHUCK UCK
LAURA URA
FESTER STER
ARMANDO MANDO
MAJOR JOR
6 rows selected.
The rest of the target string is returned.
INPUT:
SQL> SELECT * FROM SSN_TABLE;
OUTPUT:
SSN__________
300541117
301457111
459789998
3 rows selected.
ANALYSIS:
Reading the results of the preceding output is difficultSocial Security numbers
usually have dashes. Now try something fancy and see whether you like the results:
INPUT:
SQL> SELECT SUBSTR(SSN,1,3)''SUBSTR(SSN,4,2)''SUBSTR(SSN,6,4) SSN
2 FROM SSN_TABLE;
OUTPUT:
SSN_________
300541117
301457111
459789998
3 rows selected.
NOTE: This particular use of the substr
function could come in very handy with large numbers using commas such as 1,343,178,128
and in area codes and phone numbers such as 3177872915 using dashes.
Here is another good use of the SUBSTR function. Suppose you are writing
a report and a few columns are more than 50 characters wide. You can use the SUBSTR
function to reduce the width of the columns to a more manageable size if you know
the nature of the actual data. Consider the following two examples:
INPUT:
SQL> SELECT NAME, JOB, DEPARTMENT FROM JOB_TBL;
OUTPUT:
NAME______________________________________________________________
JOB_______________________________DEPARTMENT______________________
ALVIN SMITH
VICEPRESIDENT MARKETING
1 ROW SELECTED.
ANALYSIS:
Notice how the columns wrapped around, which makes reading the results a little
too difficult. Now try this select:
INPUT:
SQL> SELECT SUBSTR(NAME, 1,15) NAME, SUBSTR(JOB,1,15) JOB,
DEPARTMENT
2 FROM JOB_TBL;
OUTPUT:
NAME________________JOB_______________DEPARTMENT_____
ALVIN SMITH VICEPRESIDENT MARKETING
Much better!
TRANSLATE
The function TRANSLATE takes three arguments: the target string, the
FROM string, and the TO string. Elements of the target string that
occur in the FROM string are translated to the corresponding element in
the TO string.
INPUT:
SQL> SELECT FIRSTNAME, TRANSLATE(FIRSTNAME
2 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
3 'NNNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA)
4 FROM CHARACTERS;
OUTPUT:
FIRSTNAME TRANSLATE(FIRST
 
kelly kelly
CHUCK AAAAA
LAURA AAAAA
FESTER AAAAAA
ARMANDO AAAAAAA
MAJOR AAAAA
6 rows selected.
Notice that the function is case sensitive.
INSTR
To find out where in a string a particular pattern occurs, use INSTR.
Its first argument is the target string. The second argument is the pattern to match.
The third and forth are numbers representing where to start looking and which match
to report. This example returns a number representing the first occurrence of O
starting with the second character:
INPUT:
SQL> SELECT LASTNAME, INSTR(LASTNAME, 'O', 2, 1)
2 FROM CHARACTERS;
OUTPUT:
LASTNAME INSTR(LASTNAME,'O',2,1)
 
PURVIS 0
TAYLOR 5
CHRISTINE 0
ADAMS 0
COSTALES 2
KONG 2
6 rows selected.
ANALYSIS:
The default for the third and fourth arguments is 1. If the third argument
is negative, the search starts at a position determined from the end of the string,
instead of from the beginning.
LENGTH
LENGTH returns the length of its lone character argument. For example:
INPUT:
SQL> SELECT FIRSTNAME, LENGTH(RTRIM(FIRSTNAME))
2 FROM CHARACTERS;
OUTPUT:
FIRSTNAME LENGTH(RTRIM(FIRSTNAME))
 
kelly 5
CHUCK 5
LAURA 5
FESTER 6
ARMANDO 7
MAJOR 5
6 rows selected.
ANALYSIS:
Note the use of the RTRIM function. Otherwise, LENGTH would
return 15 for every value.
Conversion Functions
These three conversion functions provide a handy way of converting one type of
data to another. These examples use the table CONVERSIONS.
INPUT:
SQL> SELECT * FROM CONVERSIONS;
OUTPUT:
NAME TESTNUM
 
40 95
13 23
74 68
The NAME column is a character string 15 characters wide, and TESTNUM
is a number.
TO_CHAR
The primary use of TO_CHAR is to convert a number into a character. Different
implementations may also use it to convert other data types, like Date, into a character,
or to include different formatting arguments. The next example illustrates the primary
use of TO_CHAR:
INPUT:
SQL> SELECT TESTNUM, TO_CHAR(TESTNUM)
2 FROM CONVERT;
OUTPUT:
TESTNUM TO_CHAR(TESTNUM)
 
95 95
23 23
68 68
Not very exciting, or convincing. Here's how to verify that the function returned
a character string:
INPUT:
SQL> SELECT TESTNUM, LENGTH(TO_CHAR(TESTNUM))
2 FROM CONVERT;
OUTPUT:
TESTNUM LENGTH(TO_CHAR(TESTNUM))
 
95 2
23 2
68 2
ANALYSIS:
LENGTH of a number would have returned an error. Notice the difference
between TO CHAR and the CHR function discussed earlier. CHR
would have turned this number into a character or a symbol, depending on the character
set.
TO_NUMBER
TO_NUMBER is the companion function to TO_CHAR, and of course,
it converts a string into a number. For example:
INPUT:
SQL> SELECT NAME, TESTNUM, TESTNUM*TO_NUMBER(NAME)
2 FROM CONVERT;
OUTPUT:
NAME TESTNUM TESTNUM*TO_NUMBER(NAME)
  
40 95 3800
13 23 299
74 68 5032
ANALYSIS:
This test would have returned an error if TO_NUMBER had returned a character.
Miscellaneous Functions
Here are three miscellaneous functions you may find useful.
GREATEST
and LEAST
These functions find the GREATEST or the LEAST member from a
series of expressions. For example:
INPUT:
SQL> SELECT GREATEST('ALPHA', 'BRAVO','FOXTROT', 'DELTA')
2 FROM CONVERT;
OUTPUT:
GREATEST

FOXTROT
FOXTROT
FOXTROT
ANALYSIS:
Notice GREATEST found the word closest to the end of the alphabet. Notice
also a seemingly unnecessary FROM and three occurrences of FOXTROT.
If FROM is missing, you will get an error. Every SELECT needs a
FROM. The particular table used in the FROM has three rows, so
the function in the SELECT clause is performed for each of them.
INPUT:
SQL> SELECT LEAST(34, 567, 3, 45, 1090)
2 FROM CONVERT;
OUTPUT:
LEAST(34,567,3,45,1090)

3
3
3
As you can see, GREATEST and LEAST also work with numbers.
USER
USER returns the character name of the current user of the database.
INPUT:
SQL> SELECT USER FROM CONVERT;
OUTPUT:
USER

PERKINS
PERKINS
PERKINS
There really is only one of me. Again, the echo occurs because of the number of
rows in the table. USER is similar to the date functions explained earlier
today. Even though USER is not an actual column in the table, it is selected
for each row that is contained in the table.
Summary
It has been a long day. We covered 47 functionsfrom aggregates to conversions.
You don't have to remember every functionjust knowing the general types (aggregate
functions, date and time functions, arithmetic functions, character functions, conversion
functions, and miscellaneous functions) is enough to point you in the right direction
when you build a query that requires a function.
Q&A
 Q Why are so few functions defined in the ANSI standard and so many
defined by the individual implementations?
A ANSI standards are broad strokes and are not meant to drive companies
into bankruptcy by forcing all implementations to have dozens of functions. On the
other hand, when company X adds a statistical package to its SQL and it sells well,
you can bet company Y and Z will follow suit.
Q I thought you said SQL was simple. Will I really use all of these
functions?
A The answer to this question is similar to the way a trigonometry teacher
might respond to the question, Will I ever need to know how to figure the area of
an isosceles triangle in real life? The answer, of course, depends on your profession.
The same concept applies with the functions and all the other options available with
SQL. How you use functions in SQL depends mostly on you company's needs. As long
as you understand how functions work as a whole, you can apply the same concepts
to your own queries.
Workshop
The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, "Answers to Quizzes and Exercises."
Quiz
 1. Which function capitalizes the first letter of a character string and
makes the rest lowercase?
2. Which functions are also known by the name group functions?
3. Will this query work?
SQL> SELECT COUNT(LASTNAME) FROM CHARACTERS;
 4. How about this one?
SQL> SELECT SUM(LASTNAME) FROM CHARACTERS;
 5. Assuming that they are separate columns, which function(s) would splice
together FIRSTNAME and LASTNAME?
6. What does the answer 6 mean from the following SELECT?
INPUT:
SQL> SELECT COUNT(*) FROM TEAMSTATS;
OUTPUT:
COUNT(*)
 7. Will the following statement work?
SQL> SELECT SUBSTR LASTNAME,1,5 FROM NAME_TBL;
Exercises
 1. Using today's TEAMSTATS table, write a query to determine
who is batting under .25. (For the baseballchallenged reader, batting average is
hits/ab.)
2. Using today's CHARACTERS table, write a query that will return
the following:
INITIALS__________CODE
K.A.P. 32
1 row selected.
