📄 ch04.htm
字号:
TAYLOR TAYLOR
CHRISTINE CHRISTINE
ADAMS ADAMS
COSTALES COSTALES
KONG KONG
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">SUBSTR</FONT></H3>
<P>This three-argument 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.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT FIRSTNAME, SUBSTR(FIRSTNAME,2,3)
</B> 2<B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#000000"><B></B></FONT></PRE>
<PRE><FONT COLOR="#0066FF">FIRSTNAME SUB
--------------- ---
kelly ell
CHUCK HUC
LAURA AUR
FESTER EST
ARMANDO RMA
MAJOR AJO
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>If you use a negative number as the second argument, the starting point is determined
by counting backwards from the end, like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT FIRSTNAME, SUBSTR(FIRSTNAME,-13,2)
</B> 2<B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">FIRSTNAME SU
--------------- --
kelly ll
CHUCK UC
LAURA UR
FESTER ST
ARMANDO MA
MAJOR JO
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</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> <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> <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> <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><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> <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> <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> <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> <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> <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> <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> <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> <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> <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> <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> <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=⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -