New Document
Computer Science
Computer Catlog
Oracle - TOC
  1   2   3   4   5   page(5/5)

Conversion Functions


    These three conversion functions provide a handy way of converting one type of data to another. These examples use the table CONVERT.

TO-CHAR


    The primary use of TO-CHAR is to convert a number into a character. Different implementations may also use it to convert other data types, like Date, into a character, or to include different formatting arguments. The next example illustrates the primary use of TO-CHAR:

INPUT:

SQL> SELECT TESTNUM, TO-CHAR (TESTNUM) FROM CONVERT;

ANALYSIS:

    LENGTH of a number would have returned an error. Notice the difference between TO CHAR and the CHR function discussed earlier. CHR would have turned this number into a character or a symbol, depending on the character set.

TO-NUMBER


    TO-NUMBER is the companion function to TO-CHAR, and of course, it converts a string into a number. For example:

INPUT:

SQL> SELECT NAME, TESTNUM, TESTNUM*TO-NUMBER (NAME) FROM CONVERT;

ANALYSIS:

    This test would have returned an error if TO-NUMBER had returned a character.


Miscellaneous Functions


    Here are three miscellaneous functions you may find useful.

GREATEST and LEAST


    These functions find the GREATEST or the LEAST member from a series of expressions. For example:

INPUT:

SQL> SELECT GREATEST ('ALPHA', 'BRAVO','FOXTROT', 'DELTA') FROM CONVERT;

ANALYSIS:

    Notice GREATEST found the word closest to the end of the alphabet. Notice also a seemingly unnecessary FROM and three occurrences of FOXTROT. If FROM is missing, you will get an error. Every SELECT needs a FROM. The particular table used in the FROM has three rows, so the function in the SELECT clause is performed for each of them, GREATEST and LEAST also work with numbers

INPUT:

SQL> SELECT LEAST (34, 567, 3, 45, 1090) FROM CONVERT;

USER


    USER returns the character name of the current user of the database.

INPUT:

SQL> SELECT USER FROM CONVERT;

    There really is only one of me. Again, the echo occurs because of the number of rows in the table. USER is similar to the date functions explained earlier today. Even though USER is not an actual column in the table, it is selected for each row that is contained in the table.

Summary


    We covered 47 functions--from aggregates to conversions. You don't have to remember every function--just knowing the general types (aggregate functions, date and time functions, arithmetic functions, character functions, conversion functions, and miscellaneous functions) is enough to point you in the right direction when you build a query that requires a function.
  1   2   3   4   5   page(5/5)



Google