ch04.htm

Learn SQL in an easy way...

   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&gt; <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&gt; <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&gt; <B>SELECT CONCAT(FIRSTNAME, LASTNAME) &quot;FIRST AND LAST NAMES&quot;
 </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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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&gt; <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
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&gt; <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&gt; <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.