formatting:
  1. rpad and lpad
  2. instr
  3. concat

    separator ||

  4. translate
  5. initcap
  6. lower
  7. abs
  8. nvl
  9. decode

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;

concat

SELECT CONCAT(city, state) FROM zipcode CONCAT(CITY, STATE) output: SanturcePR NorthAdamsMA .....

sep

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');