ch04.htm

Learn SQL in an easy way...

</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Remember the character field <TT>FIRSTNAME</TT> in this example is 15 characters
long. That is why you used a <TT>-13</TT> 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:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT FIRSTNAME, SUBSTR(FIRSTNAME,3)
 </B> 2<B>  FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">FIRSTNAME       SUBSTR(FIRSTN
--------------- -------------
kelly           lly
CHUCK           UCK
LAURA           URA
FESTER          STER
ARMANDO         MANDO
MAJOR           JOR
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>The rest of the target string is returned.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM SSN_TABLE;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">SSN__________
300541117
301457111
459789998
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">3 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Reading the results of the preceding output is difficult--Social Security numbers
usually have dashes. Now try something fancy and see whether you like the results:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(SSN,1,3)||'-'||SUBSTR(SSN,4,2)||'-'||SUBSTR(SSN,6,4) SSN
 </B> 2<B>  FROM SSN_TABLE;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">SSN_________
300-54-1117
301-45-7111
459-78-9998
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">3 rows selected.
</FONT></PRE>


<BLOCKQUOTE>
	<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT><B> </B>This particular use of the <TT>substr</TT>
	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 317-787-2915 using dashes. 
<HR>


</BLOCKQUOTE>

<P>Here is another good use of the <TT>SUBSTR</TT> function. Suppose you are writing
a report and a few columns are more than 50 characters wide. You can use the <TT>SUBSTR</TT>
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:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt;<B> SELECT NAME, JOB, DEPARTMENT FROM JOB_TBL;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME______________________________________________________________
JOB_______________________________DEPARTMENT______________________
ALVIN SMITH
VICEPRESIDENT                     MARKETING

1 ROW SELECTED.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice how the columns wrapped around, which makes reading the results a little
too difficult. Now try this <TT>select</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUBSTR(NAME, 1,15) NAME, SUBSTR(JOB,1,15) JOB,
            DEPARTMENT
 </B> 2<B>  FROM JOB_TBL;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME________________JOB_______________DEPARTMENT_____
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">ALVIN SMITH         VICEPRESIDENT     MARKETING
</FONT></PRE>
<P>Much better!
<H3><FONT COLOR="#000077">TRANSLATE</FONT></H3>
<P>The function <TT>TRANSLATE</TT> takes three arguments: the target string, the
<TT>FROM</TT> string, and the <TT>TO</TT> string. Elements of the target string that
occur in the <TT>FROM</TT> string are translated to the corresponding element in
the <TT>TO</TT> string.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT FIRSTNAME, TRANSLATE(FIRSTNAME
  </B>2<B>  '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ
  </B>3<B>  'NNNNNNNNNNAAAAAAAAAAAAAAAAAAAAAAAAAA)
 </B> 4<B>  FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">FIRSTNAME       TRANSLATE(FIRST
--------------- ---------------
kelly           kelly
CHUCK           AAAAA
LAURA           AAAAA
FESTER          AAAAAA
ARMANDO         AAAAAAA
MAJOR           AAAAA
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>Notice that the function is case sensitive.
<H3><FONT COLOR="#000077">INSTR</FONT></H3>
<P>To find out where in a string a particular pattern occurs, use <TT>INSTR</TT>.
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 <TT>O</TT>
starting with the second character:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT LASTNAME, INSTR(LASTNAME, 'O', 2, 1)
  </B>2<B>  FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME        INSTR(LASTNAME,'O',2,1)
--------------- -----------------------
PURVIS                                0
TAYLOR                                5
CHRISTINE                             0
ADAMS                                 0
COSTALES                              2
KONG                                  2
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>The default for the third and fourth arguments is <TT>1</TT>. If the third argument
is negative, the search starts at a position determined from the end of the string,
instead of from the beginning.
<H3><FONT COLOR="#000077">LENGTH</FONT></H3>
<P><TT>LENGTH</TT> returns the length of its lone character argument. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT FIRSTNAME, LENGTH(RTRIM(FIRSTNAME))
 </B> 2<B>  FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">FIRSTNAME       LENGTH(RTRIM(FIRSTNAME))
--------------- ------------------------
kelly                                  5
CHUCK                                  5
LAURA                                  5
FESTER                                 6
ARMANDO                                7
MAJOR                                  5
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Note the use of the <TT>RTRIM</TT> function. Otherwise, <TT>LENGTH</TT> would
return <TT>15</TT> for every value.
<H2><FONT COLOR="#000077">Conversion Functions</FONT></H2>
<P>These three conversion functions provide a handy way of converting one type of
data to another. These examples use the table <TT>CONVERSIONS</TT>.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT * FROM CONVERSIONS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME              TESTNUM
--------------- ---------
40                     95
13                     23
74                     68
</FONT></PRE>
<P>The <TT>NAME</TT> column is a character string 15 characters wide, and <TT>TESTNUM</TT>
is a number.
<H3><FONT SIZE="5" COLOR="#000077"><TT>TO_CHAR</TT></FONT></H3>
<P>The primary use of <TT>TO_CHAR</TT> 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 <TT>TO_CHAR</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT TESTNUM, TO_CHAR(TESTNUM)
 </B> 2<B>  FROM CONVERT;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">  TESTNUM TO_CHAR(TESTNUM)
--------- ----------------
       95               95
       23               23
       68               68
</FONT></PRE>
<P>Not very exciting, or convincing. Here's how to verify that the function returned
a character string:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT TESTNUM, LENGTH(TO_CHAR(TESTNUM))
</B>  2 <B> FROM CONVERT;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">  TESTNUM LENGTH(TO_CHAR(TESTNUM))
--------- ------------------------
       95                        2
       23                        2
       68                        2
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P><TT>LENGTH</TT> of a number would have returned an error. Notice the difference
between <TT>TO CHAR</TT> and the <TT>CHR</TT> function discussed earlier. <TT>CHR</TT>
would have turned this number into a character or a symbol, depending on the character
set.
<H3><FONT SIZE="5" COLOR="#000077"><TT>TO_NUMBER</TT></FONT></H3>
<P><TT>TO_NUMBER</TT> is the companion function to <TT>TO_CHAR</TT>, and of course,
it converts a string into a number. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT NAME, TESTNUM, TESTNUM*TO_NUMBER(NAME)
</B>  2<B>  FROM CONVERT;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">NAME             TESTNUM TESTNUM*TO_NUMBER(NAME)
--------------- -------- -----------------------
40                    95                    3800
13                    23                     299
74                    68                    5032
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>This test would have returned an error if <TT>TO_NUMBER</TT> had returned a character.
<H2><FONT COLOR="#000077">Miscellaneous Functions</FONT></H2>
<P>Here are three miscellaneous functions you may find useful.
<H3><FONT SIZE="5" COLOR="#000077"><TT>GREATEST</TT></FONT><FONT COLOR="#000077">
and </FONT><FONT SIZE="5" COLOR="#000077"><TT>LEAST</TT></FONT></H3>
<P>These functions find the <TT>GREATEST</TT> or the <TT>LEAST</TT> member from a
series of expressions. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT GREATEST('ALPHA', 'BRAVO','FOXTROT', 'DELTA')
  </B>2 <B> FROM CONVERT;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">GREATEST
-------
FOXTROT
FOXTROT
FOXTROT
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Notice <TT>GREATEST</TT> found the word closest to the end of the alphabet. Notice
also a seemingly unnecessary <TT>FROM</TT> and three occurrences of <TT>FOXTROT</TT>.
If <TT>FROM</TT> is missing, you will get an error. Every <TT>SELECT</TT> needs a
<TT>FROM</TT>. The particular table used in the <TT>FROM</TT> has three rows, so
the function in the <TT>SELECT</TT> clause is performed for each of them.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT LEAST(34, 567, 3, 45, 1090)
</B>  2 <B> FROM CONVERT;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">LEAST(34,567,3,45,1090)
-----------------------
                      3
                      3
                      3
</FONT></PRE>
<P>As you can see, <TT>GREATEST</TT> and <TT>LEAST</TT> also work with numbers.
<H3><FONT COLOR="#000077">USER</FONT></H3>
<P><TT>USER</TT> returns the character name of the current user of the database.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT USER FROM CONVERT;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">USER
------------------------------
PERKINS
PERKINS
PERKINS
</FONT></PRE>
<P>There really is only one of me. Again, the echo occurs because of the number of
rows in the table. <TT>USER</TT> is similar to the date functions explained earlier
today. Even though <TT>USER</TT> is not an actual column in the table, it is selected
for each row that is contained in the table.
<H2><FONT COLOR="#000077">Summary</FONT></H2>
<P>It has been a long day. We covered 47 functions--from aggregates to conversions.
You don't have to remember every function--just 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.
<H2><FONT COLOR="#000077">Q&amp;A</FONT></H2>

<DL>
	<DD><B>Q</B> <B>Why are so few functions defined in the ANSI standard and so many
	defined by the individual implementations?</B>
	<P><B>A</B> 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.</P>
	<P><B>Q</B> <B>I thought you said SQL was simple. Will I really use all of these
	functions?</B></P>
	<P><B>A</B> 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.
</DL>

<H2><FONT COLOR="#000077">Workshop</FONT></H2>
<P>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, &quot;Answers to Quizzes and Exercises.&quot;
<H3><FONT COLOR="#000077">Quiz</FONT></H3>

<DL>
	<DD><B>1.</B> Which function capitalizes the first letter of a character string and
	makes the rest lowercase?
	<P><B>2.</B> Which functions are also known by the name group functions?</P>
	<P><B>3.</B> Will this query work?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT COUNT(LASTNAME) FROM CHARACTERS;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>4.</B> How about this one?
</DL>



<BLOCKQUOTE>
	<PRE><FONT COLOR="#0066FF">SQL&gt; <B>SELECT SUM(LASTNAME) FROM CHARACTERS;</B></FONT></PRE>

</BLOCKQUOTE>


<DL>
	<DD><B>5. </B>Assuming that they are separate columns, which function(s) would splice
	together <TT>FIRSTNAME</TT> and <TT>LASTNAME</TT>?
	<P><B>6.</B> What does the answer <TT>6</TT> mean from the following <TT>SELECT</TT