CS 450/550 Database Concepts
SQL String Functions
These functions can be used within sql or pl/sql statements. For example:
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.
Returns string with the leftmost or rightmost characters that match the
characters in trimlist removed. Trimlist may be omitted: defaults to single
Returns string with all occurances of target replaced with replacement. If
replacement is omitted, occurances are deleted.
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.
Returns string with each character in the fromlist replaced with the
corresponding character in the tolist.
formats the number according to the format string (which must be within
Some Numeric Formats
||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 '#######'
||show a '$' before each number in the column.
||use commas and decimals according to the pattern shown.
Some Date Formats
See Oracle documentation (links on home page) for more details.
|MONTH DD, YYYY
||AUGUST 24, 2011
Character functions Returning Numeric Values
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.
Returns length of string in characters.
Miscellaneous Useful Functions
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;
-- but when everything summed is null, it returns null
SQL> select sum(hours) from works_on
2 where hours is null;
-- to make nulls behave like 0, use NVL:
SQL> select sum(nvl(hours,0)) from works_on
2 where hours is null;
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;
SQL> select to_char(123456789,'990.90') from dual;