SELECT RPAD(city, 20, '*') "City Name",
LPAD(state, 10, '-') "State Name"
FROM zipcode;
format for LPAD:
LPAD(char1, n [, char2])
RPAD(char1, n [, char2])
SELECT LPAD(city, 20) as "City Name", state FROM zipcode;
output: city Name ST
----------------- --
Santurce NY
substr
SELECT last_name, SUBSTR(last_name, 1, 5),
SUBSTR(last_name, 6) FROM student;
2nd param: start position
3rd param: optional/length of subset
instr
SELECT description, INSTR(description, 'er') FROM course;
SELECT city || state || zip FROM zipcode;
output: puts them all together with no whitespace between them ,same as above
CITY||','||STATE||''||ZIP
instr
SELECT description, INSTR(description, 'er') FROM course;
output: 2 columns the latter one titled INSTR(description, 'er') and containing a number
translate
TRANSLATE provides one-for-one character substitution using if-then-else logic
SELECT phone FROM student WHERE
TRANSLATE( phone, '01234567890', '###########-') <> '###-###-####';
lower
SELECT state, LOWER(state), LOWER('State') FROM zipcode;
INITCAP
output: capilatizes just the first letter of the argument
abs
SELECT 'the absolute of -29 is '|| ABS(-29) FROM dual;
round
SELECT 222.34501, ROUND(222.34501,2), TRUNC(222.34501,2)
FROM DUAL
nvl
replaces a NULL with a default value NVL(input_expression, substitution_expression
SELECT 60+60+NVL(NULL,1000) FROM dual
SELECT course_no, description,
NVL(prerequisite, 'Not Applicable') prereq
FROM course
WHERE course_no IN (20,100)
output: error, must --- >
NVL(TO_CHAR(prerequisite), 'Not Applicable') prereq
decode
The decode function substitutes values based on a condition using if-then-else logic.
DECODE(if_expr, equals_search, then_result [, else_default])
DECODE(state, 'NY', 'New York',
'NJ', 'New Jersey') no_default,
DECODE((state, 'NY', 'New York',
'NJ', 'New Jersey'
'OTHER') with_default,
FROM zipcode
WHERE state IN ('NY', 'NJ', 'CT');
