📄 ch04.htm
字号:
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">7 rows selected.
</FONT></PRE>
<P>You could also use <TT>SIGN</TT> in a <TT>SELECT WHERE</TT> clause like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A
</B> 2<B> FROM NUMBERS
</B> 3 <B> WHERE SIGN(A) = 1;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> A
---------
3.1415
5
15
</FONT></PRE>
<H3><FONT COLOR="#000077">SQRT</FONT></H3>
<P>The function <TT>SQRT</TT> returns the square root of an argument. Because the
square root of a negative number is undefined, you cannot use <TT>SQRT</TT> on negative
numbers.</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT A, SQRT(A)
</B> 2<B> FROM NUMBERS;
</B></FONT></PRE>
<PRE><FONT COLOR="#0066FF">ERROR:
ORA-01428: argument '-45' is out of range
</FONT></PRE>
<P>However, you can fix this limitation with <TT>ABS</TT>:</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT ABS(A), SQRT(ABS(A))
</B> 2<B> FROM NUMBERS;
</B>
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
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">7 rows selected.
</FONT></PRE>
<H2><FONT COLOR="#000077">Character Functions</FONT></H2>
<P>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 <TT>CHARACTERS</TT>.</P>
<H5>INPUT/<FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT * FROM CHARACTERS;</B>
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">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
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">CHR</FONT></H3>
<P><TT>CHR</TT> 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 <TT>CODE</TT> includes numbers.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT CODE, CHR(CODE)
</B> 2 <B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF"> CODE CH
--------- --
32
67 C
65 A
87 W
77 M
52 4
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>The space opposite the <TT>32</TT> shows that <TT>32</TT> is a space in the ASCII
character set.
<H3><FONT COLOR="#000077">CONCAT</FONT></H3>
<P>You used the equivalent of this function on Day 3, when you learned about operators.
The <TT>||</TT> symbol splices two strings together, as does <TT>CONCAT</TT>. It
works like this:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT CONCAT(FIRSTNAME, LASTNAME) "FIRST AND LAST NAMES"
</B> 2<B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">FIRST AND LAST NAMES
------------------------
KELLY PURVIS
CHUCK TAYLOR
LAURA CHRISTINE
FESTER ADAMS
ARMANDO COSTALES
MAJOR KONG
6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Quotation marks surround the multiple-word alias <TT>FIRST AND LAST NAMES</TT>.
Again, it is safest to check your implementation to see if it allows multiple-word
aliases.</P>
<P>Also notice that even though the table looks like two separate columns, what you
are seeing is one column. The first value you concatenated, <TT>FIRSTNAME</TT>, is
15 characters wide. This operation retained all the characters in the field.
<H3><FONT COLOR="#000077">INITCAP</FONT></H3>
<P><TT>INITCAP</TT> capitalizes the first letter of a word and makes all other characters
lowercase.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT FIRSTNAME BEFORE, INITCAP(FIRSTNAME) AFTER
</B> 2 <B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">BEFORE AFTER
-------------- ----------
KELLY Kelly
CHUCK Chuck
LAURA Laura
FESTER Fester
ARMANDO Armando
MAJOR Major
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H3><FONT COLOR="#000077">LOWER and UPPER</FONT></H3>
<P>As you might expect, <TT>LOWER</TT> changes all the characters to lowercase; <TT>UPPER</TT>
does just the reverse.</P>
<P>The following example starts by doing a little magic with the <TT>UPDATE</TT>
function (you learn more about this next week) to change one of the values to lowercase:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>UPDATE CHARACTERS
</B> 2<B> SET FIRSTNAME = 'kelly'
</B> 3<B> WHERE FIRSTNAME = 'KELLY';</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">1 row updated.
</FONT></PRE>
<H5><FONT COLOR="#000000">INPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT FIRSTNAME
</B> 2 <B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">FIRSTNAME
---------------
kelly
CHUCK
LAURA
FESTER
ARMANDO
MAJOR
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>Then you write</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT FIRSTNAME, UPPER(FIRSTNAME), LOWER(FIRSTNAME)
</B>2<B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">FIRSTNAME UPPER(FIRSTNAME LOWER(FIRSTNAME
--------------- --------------- ---------------
kelly KELLY kelly
CHUCK CHUCK chuck
LAURA LAURA laura
FESTER FESTER fester
ARMANDO ARMANDO armando
MAJOR MAJOR major
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>Now you see the desired behavior.
<H3><FONT SIZE="5" COLOR="#000077"><TT>LPAD</TT></FONT><FONT COLOR="#000077"> and
</FONT><FONT SIZE="5" COLOR="#000077"><TT>RPAD</TT></FONT></H3>
<P><TT>LPAD</TT> and <TT>RPAD</TT> 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 <TT>LASTNAME</TT> is defined as a 15-character field:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT LASTNAME, LPAD(LASTNAME,20,'*')
</B> 2<B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME LPAD(LASTNAME,20,'*'
-------------- --------------------
PURVIS *****PURVIS
TAYLOR *****TAYLOR
CHRISTINE *****CHRISTINE
ADAMS *****ADAMS
COSTALES *****COSTALES
KONG *****KONG
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>Why were only five pad characters added? Remember that the <TT>LASTNAME</TT> column
is 15 characters wide and that <TT>LASTNAME</TT> 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:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT LASTNAME, RPAD(LASTNAME,20,'*')
</B> 2<B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME RPAD(LASTNAME,20,'*'
--------------- --------------------
PURVIS PURVIS *****
TAYLOR TAYLOR *****
CHRISTINE CHRISTINE *****
ADAMS ADAMS *****
COSTALES COSTALES *****
KONG KONG *****
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<H5>ANALYSIS:</H5>
<P>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.
<H3><FONT SIZE="6" COLOR="#000077"><TT>LTRIM</TT></FONT><FONT COLOR="#000077"> and
</FONT><FONT SIZE="6" COLOR="#000077"><TT>RTRIM</TT></FONT></H3>
<P><TT>LTRIM</TT> and <TT>RTRIM</TT> take at least one and at most two arguments.
The first argument, like <TT>LPAD</TT> and <TT>RPAD</TT>, 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.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT LASTNAME, RTRIM(LASTNAME)
</B> 2<B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME RTRIM(LASTNAME)
--------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRISTINE
ADAMS ADAMS
COSTALES COSTALES
KONG KONG
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>You can make sure that the characters have been trimmed with the following statement:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT LASTNAME, RPAD(RTRIM(LASTNAME),20,'*')
</B> 2<B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME RPAD(RTRIM(LASTNAME)
--------------- --------------------
PURVIS PURVIS**************
TAYLOR TAYLOR**************
CHRISTINE CHRISTINE***********
ADAMS ADAMS***************
COSTALES COSTALES************
KONG KONG****************
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>The output proves that trim is working. Now try <TT>LTRIM</TT>:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT LASTNAME, LTRIM(LASTNAME, 'C')
</B> 2 <B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME LTRIM(LASTNAME,
--------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE HRISTINE
ADAMS ADAMS
COSTALES OSTALES
KONG KONG
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>Note the missing Cs in the third and fifth rows.
<H3><FONT COLOR="#000077">REPLACE</FONT></H3>
<P><TT>REPLACE</TT> 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 <TT>NULL</TT>, each occurrence of the
search key on the string to be searched is removed and is not replaced with anything.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT LASTNAME, REPLACE(LASTNAME, 'ST') REPLACEMENT
</B> 2 <B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000"><B>OUTPUT:</B></FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME REPLACEMENT
--------------- ---------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRIINE
ADAMS ADAMS
COSTALES COALES
KONG KONG
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>If you have a third argument, it is substituted for each occurrence of the search
key in the target string. For example:</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT LASTNAME, REPLACE(LASTNAME, 'ST','**') REPLACEMENT
</B> 2 <B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME REPLACEMENT
--------------- ------------
PURVIS PURVIS
TAYLOR TAYLOR
CHRISTINE CHRI**INE
ADAMS ADAMS
COSTALES CO**ALES
KONG KONG
</FONT></PRE>
<PRE><FONT COLOR="#0066FF">6 rows selected.
</FONT></PRE>
<P>If the second argument is <TT>NULL</TT>, the target string is returned with no
changes.</P>
<H5>INPUT:</H5>
<PRE><FONT COLOR="#0066FF">SQL> <B>SELECT LASTNAME, REPLACE(LASTNAME, NULL) REPLACEMENT
</B> 2 <B> FROM CHARACTERS;</B></FONT></PRE>
<H5><FONT COLOR="#000000">OUTPUT:</FONT></H5>
<PRE><FONT COLOR="#0066FF">LASTNAME REPLACEMENT
--------------- ---------------
PURVIS PURVIS
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -