formatting

<ol> <li><a href="#rpad">rpad and lpad</a>

<li><a href="instr">instr</a>
<li><a href="concat">concat</a>
<h3><a name="sep"></a>separator ||<h3>
<li><a href="translate">translate</a>
<li><a href="initcap">initcap</a>
<LI><a href="lower">lower</a>

<li><a href="abs">abs</a>
<li><a href="round>round</a>

<li>
<li><a href="nvl">nvl</a>
<li><a href="decode">decode</a>
</ol>
<h3><a name=""></a><h3>
SELECT RPAD(city, 20, '*') "City Name",

LPAD(state, 10, '-') "State Name"
FROM zipcode;

format for LPAD:
LPAD(char1, n [, char2])
RPAD(char1, n [, char2])

SELECT LPAD(city, 20) as "City Name", state FROM zipcode;

output: city Name         ST
        ----------------- --
                Santurce  NY    

<h3><a name="substr"></a>substr<h3>
SELECT last_name, SUBSTR(last_name, 1, 5),

SUBSTR(last_name, 6) FROM student;

2nd param: start position
3rd param: optional/length of subset

<h3><a name="instr"></a>instr<h3>
SELECT description, INSTR(description, 'er') FROM course;

<h3><a name="concat"></a>concat<h3>
SELECT CONCAT(city, state) FROM zipcode CONCAT(CITY, STATE)

output

SanturcePR NorthAdamsMA .....

<h3><a name="sep"></a>sep<h3>
SELECT city || state || zip FROM zipcode;

output: puts them all together with no whitespace between them ,same as above

CITY||','||STATE||''||ZIP

<h3><a name="instr"></a>instr<h3>
SELECT description, INSTR(description, 'er') FROM course;
output: 2 columns the latter one titled INSTR(description, 'er') and containing a number

<h3><a name="translate"></a>translate<h3>
TRANSLATE provides one-for-one character substitution using if-then-else logic

SELECT phone FROM student WHERE
TRANSLATE( phone, '01234567890', '##########-') <> '##-###-####';

<h3><a name="lower"></a>lower<h3>
SELECT state, LOWER(state), LOWER('State') FROM zipcode;
INITCAP
output: capilatizes just the first letter of the argument

<h3><a name="abs"></a>abs<h3>

SELECT 'the absolute of -29 is '|| ABS(-29) FROM dual;</pre>
<h3><a name="round"></a>round</h3>
<pre>
SELECT 222.34501, ROUND(222.34501,2), TRUNC(222.34501,2) 
FROM DUAL

<h3><a name="nvl"></a>nvl</h3>
replaces a NULL with a default value<br>
<tt>NVL(input_expression, substitution_expression</tt><br>
SELECT 60+60+NVL(NULL,1000) FROM dual<br>
SELECT course_no, description, <br>
NVL(prerequisite, 'Not Applicable') prereq<br>
FROM course<br>
WHERE course_no IN (20,100)<br>
output: error, must --- &gt;<br>
NVL(TO_CHAR(prerequisite), 'Not Applicable') prereq<br>

<h3><a name="decode"></a>decode</h3>
The decode function substitutes values based on a condition using if-then-else logic.<br>
DECODE(if_expr, equals_search, then_result [, else_default])<br>
DECODE(state, 'NY', 'New York',<br>

'NJ', 'New Jersey') no_default,<br>
DECODE((state, 'NY', 'New York',<br>

                'NJ', 'New Jersey'
                        'OTHER') with_default,<br>

FROM zipcode<BR>
WHERE state IN ('NY', 'NJ', 'CT');<br>
<h3><a name=""></a></h3>
<h3><a name=""></a></h3>
<h3><a name=""></a></h3>
<h3><a name=""></a></h3>
<h3><a name=""></a></h3>
<h3><a name=""></a></h3>