Key Oracle SQL Functions

You use Oracle functions to accomplish many programming tasks from within your SQL statements. We've already mentioned the TO_DATE function, which is a Conversion function that converts one datatype to another. Conversion functions are just the tip of the iceberg. Oracle also has Grouping, Numeric, String, and Date functions (and many, many more). Being able to take advantage of Oracle's internal functions in your SQL statements has tremendous advantages. Let's take a look at the functions you might use in a typical web application.

Grouping Functions

You use Grouping functions to return values from groups of records defined in a SQL statement. Key Grouping functions include AVG, COUNT, MAX, MIN, and SUM.

AVG(expression)

AVG returns the average of the values in a set of rows. You can specify DISTINCT or ALL in the AVG parameters. ALL (the default) uses all rows in the set of rows to calculate the average. DISTINCT uses only unique row values to calculate the average. For example, the following SQL statement discounts row value duplicates and returns an alias holding the value of 3 as the DISTINCT average of (2,2,3,3,4). (An alias is a name that stands for a column or table. In our example, we create an alias named NUM_PAGES_AVG for the NUM_PAGES.)

SELECT  AVG(DISTINCT NUM_PAGES) AS NUM_PAGES_AVG  FROM BOOK

COUNT (expression)

COUNT returns the number of rows in a recordset. If you include a column in the parameter, COUNT returns the number of rows in which the row value is not NULL. To demonstrate COUNT, the following SQL statement returns an alias holding the value of the total number of rows in the table.SELECT COUNT(*) AS COLUMN_1_COUNT FROMYOURTABLELikewise, the following SQL statement returns an alias holding the value of the total number of rows where COLUMN_1 is not NULL in the table.

SELECT  COUNT(COLUMN_1) AS COLUMN_1_COUNT  FROM YOURTABLE

In some cases, Oracle's COUNT function can return decimal places with the generated count. To prevent such occurrences, wrap the COUNT function with the TO_CHAR function to drop the decimal places from the count value. For example, the following SQL code returns an alias holding the count value as a character datatype:

SELECT  TO_CHAR(COUNT(COLUMN_1)) AS COLUMN_1_COUNT  FROM YOURTABLE "

MAX(expression)

MAX returns the largest value from a set of rows. For example, the following SQL statement returns an alias holding the largest value in COLUMN_1.

SELECT  MAX(COLUMN_1) AS COLUMN_1_MAX  FROM YOURTABLE)

MIN(expression)

MIN returns the smallest value from a set of rows. For example, the following SQL statement returns an alias holding the smallest value in COLUMN_1.

SELECT MIN(COLUMN_1) AS COLUMN_1_MIN  FROM YOURTABLE)

SUM(expression)

SUM returns the total value for all values in a column. The following SQL statement returns an alias holding the total value of all values in COLUMN_1.

SELECT SUM(COLUMN_1) AS COLUMN_1_SUM  FROM YOURTABLE)

Numeric Functions

Although most scripting languages have their own set of numeric functions, passing the numeric function responsibility to an ultra-powerful database server such as Oracle can boost performance. Oracle key Numeric functions are ABS, GREATEST, LEAST, ROUND, and TRUNC.

ABS(number)

ABS removes the negative sign (if present) from a number and returns a positive value. For example, the following SQL statement returns an alias holding a positive value regardless of the actual COLUMN_1 value.

SELECT ABS(COLUMN_1) AS COLUMN_1_ABS  FROM YOURTABLE

GREATEST(value1, value2)

GREATEST returns the largest value in a list of values. To demonstrate, the following SQL statement returns an alias holding the greater value of COLUMN_1 and COLUMN_2.

SELECT GREATEST(COLUMN_1, COLUMN_2) AS  COLUMN_1_GREATEST  FROM YOURTABLE

LEAST(value1, value2)

LEAST returns the smallest value in a list of values. For example, the following SQL statement returns an alias holding the least value of COLUMN_1 and COLUMN_2.

SELECT LEAST(COLUMN_1, COLUMN_2) AS COLUMN_1_LEAST  FROM YOURTABLE 

Note 

The GREATEST and LEAST functions are meant for the greatest or least values in the same row. If you want to return the greatest or least value in a set of rows, use MAX or MIN.

ROUND(number, decimal places)

ROUND returns a value rounded to a specified number of decimal places. The first parameter is the target number, and the second parameter specifies the number of decimals to round to. For example, the following SQL statement returns an alias holding the value 123.46—the 2-decimal rounded value of 123.456.

SELECT ROUND(123.456,2) AS COLUMN_1_ROUND  FROM YOURTABLE

TRUNC(number, decimal places)

TRUNC returns a value shortened to a specified number of decimal places. The first parameter is the target number, and the second parameter specifies the number of decimals to truncate. For example, the following SQL statement returns an alias holding the value 123.45—the 2-decimal truncated value of 123.456.

SELECT TRUNC(123.456,2) AS COLUMN_1_TRUNC  FROM YOURTABLE

String Functions

Many times you will want to manipulate a character string before displaying, inserting, or updating. You use String functions to apply string manipulations on entire columns of data. Oracles String functions are ||, INITCAP, LENGTH, LOWER, SUBSTR, and UPPER.

string || string

The || function appends several character values together. For example, the following SQL statement returns an alias holding the values in COLUMN_1, COLUMN_2, and COLUMN_3.

SELECT  (COLUMN_1 || ', ' || COLUMN_2 || ', ' || COLUMN_3) AS COLUMN_1_2_3  FROM YOURTABLE

INITCAP(string)

INITCAP capitalizes the first letter of each word in a character string. This is a superb time-saver for capping the first letter in proper names. For example, the following SQL statement returns an alias holding "Macromedia Dreamweaver" if it finds the value "macromedia dreamweaver" in COLUMN_1.

SELECT INITCAP(COLUMN_1) AS COLUMN_1_INITCAP  FROM YOURTABLE

LENGTH(string)

LENGTH returns the number of characters in a character string. For example, the following SQL statement returns an alias holding the number of characters in COLUMN_1 of each row.

SELECT LENGTH(COLUMN_1) AS COLUMN_1_LENGTH  FROM YOURTABLE

LOWER(string)

LOWER converts all characters in a character string to lowercase. For example, the following SQL statement returns an alias holding "macromedia dreamweaver" from the value "Macromedia Dreamweaver" in COLUMN_1.

SELECT LOWER(COLUMN_1) AS COLUMN_1_LOWER  FROM YOURTABLE

SUBSTR(string, starting value, number of characters)

SUBSTR extracts a section of a character string. The first parameter specifies the target character string. The second parameter sets the starting position of the substring you want to extract. The third parameter sets the number of characters to extract. For example, the following SQL statement returns an alias holding "Macro" from the value "Macromedia Dreamweaver" in COLUMN_1.

SELECT SUBSTR(COLUMN_1,1,5) AS COLUMN_1_ SUBSTR  FROM YOURTABLE

UPPER(string)

UPPER converts all characters in a character string to uppercase. For example, the following SQL statement returns an alias holding "MACROMEDIA DREAMWEAVER" from the value "Macromedia Dreamweaver" in COLUMN_1.

SELECT UPPER(COLUMN_1) AS COLUMN_1_UPPER  FROM YOURTABLE

Date Functions

Often in your web application, you will need to manipulate a date/time value within a SQL statement. The key Oracle Date functions are ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN, NEXT_DAY, ROUND, SYSDATE, and TRUNC.

ADD_MONTHS (date, number of months)

ADD_MONTHS adds (or subtracts if the value is negative) a number of months to a date value. For example, the following SQL statement returns an alias holding a value that is two months beyond the date in COLUMN_1.

SELECT ADD_MONTH(COLUMN_1,2) AS COLUMN_1_ADD_MONTHS  FROM YOURTABLE

LAST_DAY(date)

LAST_DAY returns the last day of the current month value. For example, the following SQL statement returns an alias holding the last day of the month in COLUMN_1.

SELECT LAST_DAY(COLUMN_1) AS COLUMN_1_LAST_DAY  FROM YOURTABLE

MONTHS_BETWEEN(date1, date2)

MONTHS_BETWEEN returns the difference between the two dates. To demonstrate, the following SQL statement returns an alias holding the months' difference between COLUMN_1 and COLUMN_2:

SELECT MONTHS_BETWEEN(COLUMN_1, COLUMN_2) AS  COLUMN_1_MONTHS_BETWEEN  FROM YOURTABLE

NEXT_DAY(date, day name)

NEXT_DAY returns the first occurrence of the specified day after the date supplied. For example, the following SQL statement returns an alias holding the date the next Monday occurs after the supplied date.

SELECT NEXT_DAY('10-MAY-2002','MONDAY') AS COLUMN_1_NEXT_DAY  FROM YOURTABLE

ROUND(date/time, format)

ROUND returns the date/time rounded to a specified time unit. For example, the following SQL statement returns an alias holding the value "01-JAN-2002"—the rounded value of "10-NOV-2001".

SELECT ROUND('10-NOV-2001','YEAR') AS COLUMN_1_ROUND  FROM YOURTABLE 

SYSDATE

SYSDATE returns the current date/time from the database server. For example, the following SQL statement returns an alias holding today's date/time value.

SELECT SYSDATE AS COLUMN_1_ SYSDATE  FROM YOURTABLE

TRUNC(date/time)

TRUNC removes the time component from the supplied date/time value. For example, the following SQL statement returns an alias holding the value "15-AUG-2002" from the date/time value in COLUMN_1.

SELECT TRUNC(COLUMN_1) AS COLUMN_1_TRUNC  FROMY OURTABLE

Conversion Functions

As we've mentioned, Oracle groups its datatypes into three families—character, numeric, and date. As such, you can convert one datatype to another using the Oracle functions TO_DATE, TO_CHAR, and TO_NUMBER.

TO_DATE (string, format)

TO_DATE performs character-to-date conversion. For example, the following SQL statement uses the Oracle To_Date function to convert the character variable to an Oracle compliant date.

INSERT INTO    YOURTABLE    (COLUMN_1,    COLUMN_2)  VALUES    (COLUMN_1_VALUE,    TO_DATE(VARIABLE,'DD-MM-YYYY')

TO_CHAR(date, format)

TO_CHAR converts numeric and date data to the CHAR datatype. For example, the following SQL statement uses the TO_CHAR function to convert the date variable to a formatted character string.

INSERT INTO    YOURTABLE    (COLUMN_1,    COLUMN_2)  VALUES    (COLUMN_1_VALUE,    TO_CHAR(VARIABLE, `MONTH DD, YYYY') 

TO_CHAR(number, format)

Likewise, the following SQL statement uses the TO_CHAR function to convert the numeric variable to a formatted character string.

INSERT INTO    YOURTABLE    (COLUMN_1,    COLUMN_2)  VALUES    (COLUMN_1_VALUE,    TO_CHAR(VARIABLE, '$9,999,999')

TO_NUMBER(string, format)

TO_NUMBER converts character data to the numeric datatype. For example, the following SQL statement uses TO_NUMBER to convert the character variable to a number.

INSERT INTO    YOURTABLE    (COLUMN_1,     COLUMN_2)  VALUES    (COLUMN_1_VALUE,    TO_NUMBER(VARIABLE)

Favorite Functions and Features

We've shown you the functions you will most likely use in your web development efforts. Now we want to introduce you to two functions, NVL and DECODE, that don't fit neatly into the previous function categories. All the same, we use these functions so often that we want to share them. In addition, we'll also show you a useful feature called ROWNUM.

NVL(expr1, expr2)

NVL (NULL value) is extremely useful for handling NULLs in your database. NVL allows you to specify how Oracle returns a NULL value. For example, the following SQL statement returns an alias holding 0 when a NULL is found in COLUMN_1.

SELECT NVL(COLUMN_1,0) AS COLUMN_1_NVL  FROM YOURTABLE

DECODE(expr1, expr2)

You use DECODE to create a programmatic value lookup. The first parameter in DECODE is the column you're selecting. Following parameters consist of value pairs. The first value in the pair sets the value to search. The second value in the pair sets the value the SQL statement should return if the search value is found. You can also specify a default return value in case no match is found. To do so, place the default value at the end of the pair values.

To demonstrate, COLUMN_1 of our pseudotable holds gender information. Gender can have one of two values—M or F. You can use DECODE to attach the lookup values "Male" or "Female" to each M or F value in a returned recordset. As we mentioned, you can even set a default return value of "Unknown" if a row value does not match M or F. For example, the following SQL statement returns an alias holding "Male" or "Female" for each M or F value or holding "Unknown" for an unmatched value found in COLUMN_1.

SELECT DECODE(GENDER,'M','MALE','F','FEMALE','UNKNOWN') AS FULLGENDER  FROM USERS

ROWNUM

ROWNUM is a pseudocolumn that Oracle attaches to each row in a recordset returned by a SQL query. (A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.) ROWNUM contains a number identifying the order the row was retrieved from the table. For example, the first row of a recordset has a ROWNUM of 1, the second row has ROWNUM of 2, and so on. You can use ROWNUM to limit the number of records your web page displays. To demonstrate, the following SQL statement returns a recordset of the first 20 rows of a table:

SELECT COLUMN_1 FROM YOURTABLE WHERE ROWNUM < 20;

Warning 

ROWNUM contains the order in which the row was retrieved from a table. This does not necessarily mean the rows in the recordset will be displayed according to ROWNUM. For example, if we append an ORDER BY COLUMN_1 to the sample ROWNUM SQL statement, Oracle selects the first 20 rows of a table. However, Oracle also sorts the recordset according to the COLUMN_1 series—not according to the pseudocolumn ROWNUM. Therefore, the rows in the recordset will not be displayed according to the ROWNUM.

Note that ROWNUM is only useful for less-than and equal-to comparisons. Greater-than comparison will always fail. The ROWNUM pseudocolumn only exists for actual rows returned in a recordset. For example, if you attempt to select a recordset with ROWNUM > 10, your query will fail. For the ROWNUM > 10 condition to be true, your query must return at least 11 rows—which it cannot do given the condition.