Null values in columns will the affect the result in conditional
expressions given the comparison operators you use.
Null values in columns also
affect the computational result of aggregate functions.
To get around this problem, if it is a problem (determine that first).
Use NVL function.
Basic Syntax:
NVL(column/expression/variable,
substitute value);
Example of the use of NVL in a comparison:
List the book code and price of all books
whose price is not in
(8.75, 6.75). Don’t use OR operator with second condition that includes
the value if null. (Done earlier)
SQL> select
credit_limit, balance from customer;
CREDIT_LIMIT BALANCE
------------ ---------
1000 1100
1500
1000 825.75
750 770.75
1500
2000 1817.5
1500
750 402.4
1000 114.6
1000 1045.75
1500
1500
1500
1500
14 rows selected.
SQL> select credit_limit, balance from customer where balance not in (1100, 402.42 );
CREDIT_LIMIT BALANCE
------------ ---------
1000 825.75
750 770.75
2000 1817.5
1000 114.6
1000 1045.75
SQL> select credit_limit, balance from customer where nvl(balance,0) not in (1100, 402.4);
CREDIT_LIMIT BALANCE
------------ ---------
1500
1000 825.75
750 770.75
1500
2000 1817.5
1500
1000 114.6
1000 1045.75
1500
1500
1500
1500
12 rows selected.
SQL>
Give
me the minimum price for each publisher charges for a book.
If a book is not priced include it as the minimum specify the price to be
zero.
1 SELECT Publisher_code,
MIN(NVL(book_price, 0))
2 FROM book
3* GROUP BY Publisher_code
SQL> /
PU MIN(NVL(BOOK_PRICE,0))
-- ----------------------
BB
0
BF
20.5
PB
3.95
SI
6.65
TO_DATE( 'string' or text column, <format mask>):Converts a string of text to a date.
Example:
SELECT TO_DATE(
-- DUAL is a dummy table provided by Oracle for testing some functionality without using a real table.
TO_NUMBER( ‘string’ or text column, <format mask>): Converts a string of text to a number.
SELECT TO_NUMBER ('99.99') FROM DUAL;
SELECT TO_NUMBER ('$1,000.00', '$9,999.99') FROM DUAL;
/
1000
DUAL is a reserved word you can use in place of a table. Don’t use it unless you have to. Very slow.
TO_CHAR date or number column, <format mask>): Converts either a date or a number to text.
Example1:
SELECT TO_CHAR(balance, '$99,999.99') FROM
customer;
1* SELECT TO_CHAR(NVL(balance,0), '$99,990.99') FROM customer
SQL> /
$5,500.00
$0.00
$0.00
$0.00
Example2:
SELECT TO_CHAR(order_date, 'Month dd, YYYY Day') FROM orders;
Returns:
------------------------------------
February 13, 2001 Tuesday
February 13, 2001 Tuesday
February 13, 2001 Tuesday
February 13, 2001 Tuesday
February 13, 2001 Tuesday
SUBSTR( column/value, pos, n): Returns
a subset of a string based on the starting position and string length you
specify.
Example:
SELECT
SUBSTR ('415-555-1212', 5, 8 ) FROM dual;
Returns
the phone without the area code.
--------
555-1212
INSTR(column/value, 'string'):
Returns the character
position of the first occurance of a string.
Syntax: INSTR
Example:
SELECT SUBSTR(email, 1, INSTR (email, '@') -1)
AS username
FROM ugly;
SELECT SUBSTR(email, INSTR (email, '@') +1) AS
domain_name
FROM ugly;
Assuming a name column formatted like 'last, first', i.e. 'Smith, John'
SELECT SUBSTR(name, 1, INSTR(name, ',') - 1) AS lname,
SUBSTR(name, INSTR(name, ',') + 2) AS fname
FROM ugly;
RTRIM(column/value, 'char'): removes
from the the right the trailing occurrances of char/s or a combination of
trailing chars. If ‘char/s
Example:
SELECT RTRIM(Description, 's' ) FROM
Course;
Would strip the letter e from every course
description ending with ‘e’.
LTRIM(column/value, ‘char’): removes
from the left the trailing occurances of
char/s or a combination of trailing chars.
If ‘char/s’
Example:
SELECT LTRIM(Description, 'J') FROM
Course
Would strip the letter e from every course
description ending with ‘e’.
LTRIM(SUBSTR(name, INSTR(name, ',') + 1)) AS fname
FROM ugly;
UPPER(column/value): converts
text to upper case.
Example:
SELECT lname, UPPER(lname) upper_last FROM customer;
OutPut:
LAST
UPPER_LAST
-------------------- --------------------
Adams
ADAMS
Samuels
SAMUELS
Charles
CHARLES
Daniels
DANIELS
Williams
WILLIAMS
Daniels
DANIELS
Nelson
NELSON
Dinh
DINH
Galvez
GALVEZ
Marin
MARIN
LOWER(column/value): The opposite of upper, converts all characters to lower case.
SELECT lower(name), lower(email) FROM ugly;
INITCAP(column/value): converts text to proper case.
Example:
SELECT INITCAP(name) FROM ugly;
LPAD(char1, n, char2): char1 left-padded to n, with space or char2
Example:
SELECT LPAD(balance, 20, 0) FROM customer;
RPAD(char1, n, char2): char1 right-padded to n, with space or char2
Example:
SELECT RPAD(lname, 20, '*') FROM customer;
CONCAT(char1, char2): char1 concatenated with char2
Example:
SELECT CONCAT(CONCAT (RTRIM(fname), ' '), lname) FullName
FROM customer;
OutPut:
FULLNAME
------------------------------------
Sally Adams
Ann Samuels
Don Charles
Tom Daniels
Al Williams
Sally Daniels
Mary Nelson
Tran Dinh
Mara Galvez
Dan Marin
Char || char || ‘String’: Another
way to do string concatenation.
Example:
SELECT RTRIM(fname) || ' ' || lname As FullName
FROM customer;
-- Produces the same output as above.
SELECT RPAD(RTRIM(lname) || ', ' || RTRIM(fname), 40)
FROM customer;
SELECT UPPER(RPAD(RTRIM(lname) || ', ' || RTRIM(fname), 40)),
LPAD(NVL(balance,0) * 100, 10, '0')
FROM customer_dw
SMYTH, JIM
0000050000
SMYRTH, MIKE 0000045000
DAVIS, GRAY 0000029875
EDWARDS, ANTHONY 0000040240
FRANKLIN, BENJAMIN 0000111460
GORE, AL 0000004575
ADAMS, SAMUEL 0000070000
BUSH, GEORGE 0000040240
CLINTON, WILLIAM 0000011460
DAVIS, GRAY 0000029875
EDWARDS, ANTHONY 0000040240
FRANKLIN, BENJAMIN 0000111460
GORE, AL 0000004575
SMITH, JOHN 0000045000
14 rows selected.
REPLACE (char, search, replace): Change all search with replace in char
Example:
SELECT REPLACE('uptown', 'up', 'down') FROM
dual;
Output:
REPLACE(
--------
downtown
TRANSLATE (char, from, to): char
with all characters in from translated to the corresponding character in to.
Example:
SELECT TRANSLATE('fumble', 'uf', 'aR') FROM
dual;
TRANSLATE
----------
Ramble
CHR(n): Character
represented by ASCII number n
Example:
SELECT CHR(65) FROM DUAL;
-- This Yields ‘A’ because 65 is the ASCII
value for ‘A’
ASCII(char): Returns the
ASCII set number for the character char
Example:
SELECT ASCII('A') FROM DUAL;
-- This Yields 65 because 65 is the ASCII
value for ‘A’
LENGTH(char): Number of
characters in char
Example:
SELECT lname, LENGTH (RTRIM(lname)) len_last FROM customer;
Output:
LAST
LEN_LAST
-------------------- ----------
Adams
5
Samuels
7
Charles
7
Daniels
7
Williams
8
Daniels
7
Nelson
6
Dinh
4
Galvez
6
Marin
5
SOUNDEX(char): Produces a
string representing the phonetic sound of char.
Example:
SELECT
SOUNDEX('smith') smith, SOUNDEX('smyth') smyth FROM DUAL;
Output:
SMIT SMYT
---- ---- --
Notice Both produce the same ID.
S530 S530
ABS
(n): The absolute value of n.
ROUND
(column/value, n): Rounds a column, expression, value to n decimal places
TRUNC (column/value, n): Truncates
the column or value to n decimal places.
SIGN(column/value): Returns -1 if
column, expression, or value is negative. Otherwise, returns +1.
CEIL(column/value): Finds the largest integer greater or equal to the
column/value/expression.
FLOOR(column/value): Finds the smallest
integer greater or equal to the column/value/expression.
POWER(column/value, n): Raises the column,
expression, value to the nth power.
SQRT (n):
The Square Root of n; null
if n < 0.
STDDEV (n):
Standard Deviation of n.
VARIANCE (n): Variance of n.
GREATEST (expr1, expr2):
Returns the greater of the two values.
LEAST (expr1, expr2):
Returns the least of the two.
MOD(x, y): The
modulus of X as divided by Y.
VSIZE(x):
The storage size in bytes for value X.
Date Functions:
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY HH24:MI:SS';
SYSDATE:
The current date and time.
ADD_MONTHS(d,n): Date d plus n months
Example:
SELECT ADD_MONTHS(SYSDATE, 2) FROM DUAL;
Output:
ADD_MONTH
---------
19-MAR-00
LAST_DAY(d):
Last day of month containing d.
Example:
SELECT LAST_DAY(SYSDATE) FROM DUAL;
OutPut:
LAST_DAY(
---------
31-JAN-00
NEXT_DAY(d, char): Date of the first day of the week named in char starting with
d.
Example:
SELECT NEXT_DAY (SYSDATE, 'FRIDAY') FROM DUAL;
Output:
NEXT_DAY(
---------
21-JAN-00
MONTHS_BETWEEN (d, e):
Number of Months by which e precedes d.
Example:
SELECT MONTHS_BETWEEN (SYSDATE, '01-JAN-01') FROM DUAL;
OutPut:
MONTHS_BETWEEN(SYSDATE,'01-JAN-00')
-----------------------------------
.600178091
DECODE
- the equivalent of if, then else in SQL
Syntax:
DECODE (expression, search, result, default)
Example:
SELECT part_description,
DECODE(rtrim(item_class),'HW',
'House Wares',
'SG', 'Sporting Goods',
'AO', 'Outdoor Appliances',
'AP', 'Applicances', 'NoneSpecified') Type
FROM part;
Yields:
PART_DESCRIPTION
TYPE
-------------------- ------------------
Iron
House Wares
Dartboard
Sporting Goods
Basketball
Sporting Goods
Conrpopper
House Wares
Gas Grill
Outdoor Appliances
Washer
Applicances
Griddle
House Wares
Bike
Sporting Goods
Blender
House Wares
Treadmill
Sporting Goods
OTHER FUNCTIONS:
TO_MULTI_BYTE(X):
Convert single-byte string X to multi-byte characters according to NLS
(National Language Standards).
TO_SINGLE_BYTE(X):
Convert multi-byte character X to single-byte character according to NLS
(National Language Standards).
CharToRowID(x):
Converts string of characters X into an Oracle RowID.
RowIdToChar(X):
Converst string of characters X into an Oracle RowID.
HexToRaw(X):
Converts hexadecimal (base-16) value X into raw (binary) format.
RawToHex(X):
Converts raw (binary) value X into hexadecimal (base-16) format.
Convert(x[,y[,z]]):
Executes a conversion of alphanumeric string X from the current character
set optionally specified as Z to the one specified by Y.
UID: Current User ID
USERENV(variable): Return an Oracle environmental value as a Varchar. See pages 102 to 104