Oracle Tips by Burleson

to_char (num, format)

The to_char  function also converts number to character including formatting.  Below is a list of some of the number formatting codes.

Table 2.2: Number elements and associated formats

This is actually the same as the SQL*Plus Column formatting for a number.

SELECT
  TO_CHAR(123456.123456) Ex1,
  TO_CHAR(123456.123456,'999999.99') Ex2,
  TO_CHAR(123456.123456,'$999999.00') Ex3,
  TO_CHAR(123456.123456,'99999.9999') Ex4,
  TO_CHAR(123456.123456,'9,999,999,999.000999') Ex5
FROM
  dual;

EX1           EX2        EX3         EX4         EX5
------------- ---------- ----------- ----------- ------------------123456B123456  123456.12  $123456.12 ###########     123,456.123456

Notice that to_char rounds the number if there are no sufficient digits behind the decimal point.  In example four, there are not enough digits in front of the decimal point, so to_char cannot round and cannot show the actual number; therefore, it displays the pound signs (#) to indicate that it has a number that can not be displayed in the required format.  Also, as you can see in example five, if there is a nine in the format but no digit to display, the database returns spaces.  We can also place leading zeros if we want.

SELECT
  TO_CHAR(123456.123456,'9,999,999,999.000999') Ex5,
  TO_CHAR(123456.123456,'0,000,000,000.00000000') Ex6
FROM
  dual;
 

EX5                   EX6
--------------------- -----------------------
       123,456.123456  0,000,123,456.12345600 

Now, let’s format the book_retail_price from the book table.

SELECT
  book_retail_price Ugly,
  TO_CHAR(book_retail_price,'$9,999.00') Pretty
FROM
  book;

UGLY                           PRETTY
------------------------------ ----------34B95                              $34.95
32B95                              $32.95
19B95                              $19.95
99B95                              $99.95
21B95                              $21.95
44B95                              $44.95
11B95                              $11.95
38B95                              $38.95
10B95                              $10.95
19B95                              $19.95
22B95                              $22.95
39B95                              $39.95
34B95                              $34.95
27B95                              $27.95
29B95                              $29.95
49B95                              $49.95
24B95                              $24.95
24B95                              $24.95
10B95                              $10.95
29B95                              $29.95

20 rows selected.

to_date (text, format)

The to_date  function takes text and uses the formatting codes to convert the text into a date data type.  The format is telling the database that the text is in that format.  Remember that the database stores a date as a number, so it must understand what the text is representing as part of that date.  The format codes listed in the to_char table are the same for the to_date.  We will use to_date when we start inserting data into our database, but here are a few examples.

TO_DATE('05-05-2004 8:30','MM-DD-YYYY HH24:MI')

SELECT
   TO_DATE('25-05-2004 8:30','DD-MM-YYYY HH24:MI')
 FROM
   dual;
TO_DATE('
---------
25-MAY-04 

The example above takes text and changes it into a date, which is then returned in the database default format.

to_number (text, format)

The to_number function takes text and converts it into a number.  This function is not used much because the database does this automatically.  It only needs to be used to remove formatting

SELECT
  12345,
  1,2345
FROM
  dual; 

     12345          1       2345
---------- ---------- ----------
     12345          1       2345

Here, the database is automatically converting the text to number.  Notice that the formatting confused the SQL parser into thinking that the second number was actually two numbers.  If I tried it with $12345.00, I would get an ORA-00911: invalid character error on the dollar sign.

nvl (e1,e2)

As we have already discussed, NULL become a problem when evaluating comparisons and in math.  The nvl function provides a way to work with NULL values.  The nvl function work with dates, numbers or characters, but both expressions must be the same data type.  The nvl function will return expression one if it is not NULL.  If expression one is NULL, then it returns expression two.

if (e1 != NULL) return e1;
else return e2; 

For example, if an employee’s annual salary equaled his monthly pay * 12 + his commission, then we could compute every employee’s annual pay with the query below. 

SELECT
  emp_last_name Name,
  (emp_salary*12)+comm Annual
FROM
  emp;

However, any employee that does not get a commission, say an accountant or a secretary, their annual pay would be NULL.  Why, because we added a NULL to their salary*12.  But, we can use the nvl function to get around this problem.

SELECT
  emp_last_name Name,
  (emp_salary*12)+NLV(comm,0) Annual
FROM
  emp;

Here, if the employee gets a commission the function returns comm, otherwise it returns a zero.

decode

The decode  function is used to change values.  It is used in the same way a case or switch function is used in other programming languages.  In fact, Oracle has introduced the case statement, and we will cover that below.  You will normally want to use the case function over the decode function. 

SELECT
  emp_last_name Name,
  emp_salary    Salary,
  DECODE(JOB_KEY, '100','Salsperson',
                  '200','Marketer',
                  '300','Editor',
                  '400','Manager') Job
FROM
  emp;

NAME                               SALARY JOB
------------------------------ ---------- ----------
king                                95000 Salesperson
jackson                             35000 Salesperson
korn                                28000 Marketer
linus                               45000 Marketer
tokheim                             63000 Editor
levender                            14000 Editor
johnson                             31000 Editor
baker                               51000 Manager
coleman                             73000 Manager
brannigan                           66666 Manager
 

When it comes to the decode function, formatting is the key to success.  The decode line in the example above is the same as the one below.

DECODE(JOB_KEY,'100','Salsperson','200','Marketer','300','Editor','400',-'Manager') Job

As you can see, anything but a trivial replacement will become quite difficult to debug or understand.  One other problem with decode  is it is an Oracle specific extension to SQL. 

case

The case  function is the ANSI version of decode.  It performs the same function but has syntax much like a programming language’s version of case.  It uses the syntax:

CASE selection WHEN x THEN y WHEN q THEN r ELSE z END

SELECT
  emp_last_name Name,
  emp_salary    Salary,
  CASE JOB_KEY
      WHEN 100 THEN 'Salesperson'
      WHEN 200 THEN 'Marketer'
      WHEN 300 THEN 'Editor'
      ELSE 'Manager'
      END Job
FROM
  emp;

NAME                               SALARY JOB
------------------------------ ---------- ----------king                                95000 Salesperson
jackson                             35000 Salesperson
korn                                28000 Marketer
linus                               45000 Marketer
tokheim                             63000 Editor
levender                            14000 Editor
johnson                             31000 Editor
baker                               51000 Manager
coleman                             73000 Manager
brannigan                           66666 Manager

10 rows selected.

Notice that there are no commas placed at the end of the when statements.  This is all on one line the SQL parser.  Also, job_key is a number so the evaluation criteria of the when statement must also be a number.  However, the case statement in the example above returns a character string.

All of the functions we have discussed so far have been single row functions.  They operate on every row returned, but a single row at a time.  In the next section, we move into multi row functions, which allow us to aggregate data from multiple rows. 

The above book excerpt is from:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

Col. John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm