CS 450/550 Database Concepts

SQL String Functions

These functions can be used within sql or pl/sql statements. For example:

SELECT UPPER(FNAME)
FROM EMPLOYEE;
V_UpperName := upper(v_lowername);

Functions Returning Strings

UPPER(string), LOWER(string), INITCAP(string)
Returns string in all upper case, all lower case, first letter of each word capitalized, respectively.
LPAD(string, length, pad), RPAD(string, length, pad)
Returns string padded on left or right to length characters using the pad string as padding. Pad may be omitted: defaults to single space.
LTRIM(string,trimlist), RTRIM(string,trimlist)
Returns string with the leftmost or rightmost characters that match the characters in trimlist removed. Trimlist may be omitted: defaults to single space.
REPLACE(string,target,replacement)
Returns string with all occurances of target replaced with replacement. If replacement is omitted, occurances are deleted.
SUBSTR(string,pos,len)
Returns the substring of string which begins at pos and is len characters long. If pos is negative, pos is counted from the end of string. The first position in string is 1 not 0.
TRANSLATE(string,fromlist,tolist)
Returns string with each character in the fromlist replaced with the corresponding character in the tolist.

TO_CHAR(number,'format')
formats the number according to the format string (which must be within single quotes.

Some Numeric Formats
9990.90	show 4 digits to the left of the decimal point and 2 to the right. If the value is zero, show 0.00. If the number is too big, show '#######'
$9999	show a '$' before each number in the column.
999,999,999.99	use commas and decimals according to the pattern shown.

Some Date Formats
DD-MON-YYYY HH24:MI:SS	24-AUG-2010 13:15:22
Day, MM/DD/YYYY	Tuesday, 08/24/2010
MONTH DD, YYYY	AUGUST 24, 2011

See Oracle documentation (links on home page) for more details.

Character functions Returning Numeric Values

INSTR(string,target,start,nth)
Returns the position in string, starting the search at start, of the nth occurance of target. Start and nth may be omitted and default to 1. If target is not found, 0 is returned.
LENGTH(string)
Returns length of string in characters.

Miscellaneous Useful Functions

NVL(value,substitute)
if value is null, this function is equal to substitute, otherwise it is equal to value. Value can be any Oracle datatype; substitute can be a literal, another column, or an expression but must result in the same datatype as value.

 -- ordinarily SUM ignores nulls:
SQL> select sum(hours) from works_on;

SUM(HOURS)
----------
       296

-- but when everything summed is null, it returns null
SQL> select sum(hours) from works_on
  2  where hours is null;

SUM(HOURS)
----------

-- to make nulls behave like 0, use NVL:
SQL> select sum(nvl(hours,0)) from works_on
  2  where hours is null;

SUM(NVL(HOURS,0))
-----------------
                0

Checking Out Functions in SQLPlus

You can use SQLplus to see what the output from a function will be. The following use the tiny table named DUAL which has one row and one column and exists just for this purpose.

SQL> select to_char(0,'990.90') from dual;

TO_CHAR
-------
   0.00

SQL> select to_char(123456789,'990.90') from dual;

TO_CHAR
-------
#######