Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg_selecting014.htm

Edit D:\app\Administrator\product\11.2.0\dbhome_1\oc4j\j2ee\oc4j_applications\applications\em\em\online_help\tdddg\tdddg_selecting014.htm

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
    "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<script src="./callback.js" type="text/javascript"></script>
<noscript>Your browser does not support JavaScript. This help page requires JavaScript to render correctly.</noscript>
</head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii" />
<meta http-equiv="Content-Style-Type" content="text/css" />
<meta http-equiv="Content-Script-Type" content="text/javascript" />

<meta name="generator" content="Oracle DARB XHTML Converter (Mode = ohj/ohw) - Version 5.1.1 Build 005" />
<meta name="date" content="2009-04-21T9:46:23Z" />
<meta name="robots" content="noarchive" />
<meta name="doctitle" content="Using Character Functions in Queries" />
<meta name="relnum" content="11g Release 2 (11.2)" />
<meta name="partnum" content="E10766-01" />
<link rel="copyright" href="./dcommon/html/cpyr.htm" title="Copyright" type="text/html" />
<link rel="stylesheet" href="./dcommon/css/blafdoc.css" title="Oracle BLAFDoc" type="text/css" />
<link rel="contents" href="toc.htm" title="Contents" type="text/html" />
<link rel="prev" href="tdddg_selecting013.htm" title="Previous" type="text/html" />
<link rel="next" href="tdddg_selecting015.htm" title="Next" type="text/html" />
<title>Using Character Functions in Queries</title>
</head>
<body>
<div class="zz-skip-header"><a href="#BEGIN">Skip Headers</a></div>
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr valign="bottom">
<td align="left"></td>
<td align="center"><a href="tdddg_selecting013.htm"><img width="24" height="24" src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdddg_selecting015.htm"><img width="24" height="24" src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
<p><a id="CEGHFECE" name="CEGHFECE"></a><a id="TDDDG22530" name="TDDDG22530"></a></p>
<div class="sect2">
<h1>Using Character Functions in Queries</h1>
<a name="BEGIN" id="BEGIN"></a>
<p><a id="sthref133" name="sthref133"></a>Character functions accept character input. Most return character values, but some return numeric values. Each character function returns a single value for each row that is evaluated. The character functions that SQL supports are listed and described in <a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF20032','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a>.</p>
<p>The functions <code>UPPER</code>, <code>INITCAP</code>, and <code>LOWER</code> display their character arguments in uppercase, initial capital, and lowercase, respectively.</p>
<p>The query in the following example displays <code>LAST_NAME</code> in uppercase, <code>FIRST_NAME</code> with the first character in uppercase and all others in lowercase, and <code>EMAIL</code> in lowercase.</p>
<div class="example"><a id="CHDEHJCA" name="CHDEHJCA"></a><a id="TDDDG150" name="TDDDG150"></a>
<p class="titleinexample">Changing the Case of Character Data</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT <span class="bold">UPPER(LAST_NAME)</span> "Last",
<span class="bold">INITCAP(FIRST_NAME)</span> "First",
<span class="bold">LOWER(EMAIL)</span> "E-Mail"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY EMAIL;
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
Last                      First                E-Mail
------------------------- -------------------- -------------------------
FAVIET                    Daniel               dfaviet
SCIARRA                   Ismael               isciarra
CHEN                      John                 jchen
URMAN                     Jose Manuel          jmurman
POPP                      Luis                 lpopp
GREENBERG                 Nancy                ngreenbe
 
6 rows selected.
</pre></div>

<p>The functions <code>LTRIM</code> and <code>RTRIM</code> trim characters from the left and right ends of their character arguments, respectively. The function <code>TRIM</code> trims leading zeros, trailing zeros, or both.</p>
<p>The query in the following example finds every clerk in the <code>EMPLOYEES</code> table and trims <code>'_CLERK'</code> from the <code>JOB_ID</code>, displaying only the characters that identify the type of clerk.</p>
<div class="example"><a id="CHDGDFJE" name="CHDGDFJE"></a><a id="TDDDG151" name="TDDDG151"></a>
<p class="titleinexample">Trimming Character Data</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT LAST_NAME,
<span class="bold">RTRIM(JOB_ID, '_CLERK')</span> "Clerk Type"
FROM EMPLOYEES
<span class="bold">WHERE JOB_ID LIKE '%_CLERK'</span>
ORDER BY LAST_NAME;
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
LAST_NAME                 Clerk Type
------------------------- ----------
Atkinson                  ST
Baida                     PU
Bell                      SH
Bissot                    ST
Bull                      SH
Cabrio                    SH
Chung                     SH
Colmenares                PU
Davies                    ST
Dellinger                 SH
Dilly                     SH
 
LAST_NAME                 Clerk Type
------------------------- ----------
Everett                   SH
Feeney                    SH
...
LAST_NAME                 Clerk Type
------------------------- ----------
Walsh                     SH
 
45 rows selected.
</pre></div>

<p>The functions <code>LPAD</code> and <code>RPAD</code> pad their character arguments on the left and right, respectively, with a specified character (the default character is a space).</p>
<p>The query in the following example displays <code>FIRST_NAME</code> and <code>LAST_NAME</code> in 15-character columns, blank-padding <code>FIRST_NAME</code> on the left and <code>LAST_NAME</code> on the right.</p>
<div class="example"><a id="CHDHJECJ" name="CHDHJECJ"></a><a id="TDDDG152" name="TDDDG152"></a>
<p class="titleinexample">Padding Character Data</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT <span class="bold">LPAD(FIRST_NAME,15)</span> "First",
<span class="bold">RPAD(LAST_NAME,15)</span> "Last"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY FIRST_NAME;
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
First           Last
--------------- ---------------
         Daniel Faviet
         Ismael Sciarra
           John Chen
    Jose Manuel Urman
           Luis Popp
          Nancy Greenberg
 
6 rows selected.
</pre></div>

<p>The <code>SUBSTR</code> function accepts as arguments a string, a character position, and a length, and returns the substring that starts at the specified position in the string and has the specified length.</p>
<p>The query in the following example uses <code>SUBSTR</code> to abbreviate <code>FIRST_NAME</code> to first initial and to strip the area code from <code>PHONE_NUMBER</code>.</p>
<div class="example"><a id="CHDFDJDH" name="CHDFDJDH"></a><a id="TDDDG153" name="TDDDG153"></a>
<p class="titleinexample">Extracting Substrings from Character Data</p>
<pre xml:space="preserve" class="oac_no_warn">
SELECT <span class="bold">SUBSTR(FIRST_NAME, 1, 1)</span> || '. ' || LAST_NAME "Name",
<span class="bold">SUBSTR(PHONE_NUMBER, 5, 8)</span> "Phone"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY LAST_NAME;
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
Name                         Phone
---------------------------- --------
J. Chen                      124.4269
D. Faviet                    124.4169
N. Greenberg                 124.4569
L. Popp                      124.4567
I. Sciarra                   124.4369
J. Urman                     124.4469
 
6 rows selected.
</pre></div>

<p>The <code>REPLACE</code> function replaces one substring with another.</p>
<p>The query in the following example uses the <code>SUBSTR</code> function in the <code>WHERE</code> clause to select the employees whose <code>JOB_ID</code> starts with <code>'SH'</code>, and uses the <code>REPLACE</code> function to replace <code>'SH'</code> with <code>'SHIPPING'</code> in each such <code>JOB_ID</code>.</p>
<div class="example"><a id="CHDCGECF" name="CHDCGECF"></a><a id="TDDDG154" name="TDDDG154"></a>
<p class="titleinexample">Replacing Substrings in Character Data</p>
<pre xml:space="preserve" class="oac_no_warn">
COLUMN "Job" FORMAT A15;
SELECT LAST_NAME,
<span class="bold">REPLACE(JOB_ID, 'SH', 'SHIPPING')</span> "Job"
FROM EMPLOYEES
<span class="bold">WHERE SUBSTR(JOB_ID, 1, 2) = 'SH'</span>
ORDER BY LAST_NAME;
</pre>
<p>Result:</p>
<pre xml:space="preserve" class="oac_no_warn">
LAST_NAME                 Job
------------------------- ---------------
Bell                      SHIPPING_CLERK
Bull                      SHIPPING_CLERK
Cabrio                    SHIPPING_CLERK
Chung                     SHIPPING_CLERK
Dellinger                 SHIPPING_CLERK
Dilly                     SHIPPING_CLERK
Everett                   SHIPPING_CLERK
Feeney                    SHIPPING_CLERK
Fleaur                    SHIPPING_CLERK
Gates                     SHIPPING_CLERK
Geoni                     SHIPPING_CLERK
 
LAST_NAME                 Job
------------------------- ---------------
Grant                     SHIPPING_CLERK
Jones                     SHIPPING_CLERK
McCain                    SHIPPING_CLERK
OConnell                  SHIPPING_CLERK
Perkins                   SHIPPING_CLERK
Sarchand                  SHIPPING_CLERK
Sullivan                  SHIPPING_CLERK
Taylor                    SHIPPING_CLERK
Walsh                     SHIPPING_CLERK
 
20 rows selected.
</pre></div>

<div class="helpinfonotealso">
<h2>Related Topics</h2>
<p><a href="javascript:open('http://www.oracle.com/pls/db112/lookup?id=SQLRF20032','newWindow').focus()"><span class="italic">Oracle Database SQL Language Reference</span></a></p>
<p><a href="tdddg_selecting010.htm#BCGCCGJF">Using Operators and Functions in Queries</a></p>
</div>
</div>


<div class="footer">
<table class="simple oac_no_warn" summary="" cellspacing="0" cellpadding="0" width="100%">
<col width="86%" />
<col width="*" />
<tr>
<td align="left"><span class="copyrightlogo">Copyright&nbsp;&copy;&nbsp;1996, 2009,&nbsp;Oracle&nbsp;and/or&nbsp;its&nbsp;affiliates.&nbsp;All&nbsp;rights&nbsp;reserved.</span><br />
<a href="./dcommon/html/cpyr.htm"><span class="copyrightlogo">Legal Notices</span></a></td>
<td align="center"><a href="tdddg_selecting013.htm"><img width="24" height="24" src="./dcommon/gifs/leftnav.gif" alt="Previous" /><br />
<span class="icon">Previous</span></a>&nbsp;</td>
<td align="center"><a href="tdddg_selecting015.htm"><img width="24" height="24" src="./dcommon/gifs/rightnav.gif" alt="Next" /><br />
<span class="icon">Next</span></a></td>
</tr>
</table>
</div>

</body>
</html>

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de