SQL FUNCTIONS
NOTE:[To View tables in Schema by using this Syntax: select tablespace_name, table_name from user_tables; ]
FUNCTIONS-->(1.SINGLE ROW FUNCTIONS AND 2.MULTIPLE ROW FUNCTIONS)
1.SINGLE ROW FUNCTIONS-->(1.CHARACTER 2.NUMBER 3.DATE 4.CONVERSION 5.GENERAL)
1.CHARACTER FUNCTIONS : ?ccept character input and can return both character and number values
CHARACTER FUNCTION--> 1.CASE-CONVERSION FUNCTIONS 2.CHARACTER MANIPULATION FUNCTIONS
1.CASE-CONVERSION FUNCTIONS(1.LOWER 2.UPPER 3.INITCAP)
SYNTAX:SELECT 'The job id for '||UPPER(last_name)||' is ' ||LOWER(job_id) AS "EMPLOYEE DETAILS" FROM employees;
:SELECT employee_id, last_name, department_id FROM employees WHERE LOWER(last_name) = 'higgins';
:SELECT employee_id, UPPER(last_name), department_id FROM employees WHERE INITCAP(last_name) = 'Higgins;
1.LOWER :Converts alpha character values to lowercase
2.UPPER :Converts alpha character values to Uppercase
3.INITCAP :Converts alpha character values to uppercase for the first letter of each word; all other letters in lowercase
2.CHARACTER MANIPULATION FUNCTIONS(1.CONCAT 2.SUBSTR 3.LENGTH 4.INSTR 5.LPAD/RPAD 6.TRIM 7.REPLACE)
SYNTAX:SELECT employee_id, CONCAT(first_name, last_name) NAME, job_id, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?" FROM employees WHERE SUBSTR(job_id, 4) = 'REP';
:SELECT employee_id, CONCAT(first_name, last_name) NAME, LENGTH (last_name), INSTR(last_name, 'a') "Contains 'a'?" FROM employees WHERE SUBSTR(last_name, -1, 1) = 'n';
1.CONCAT :Concatenates the first character value to the second character value; equivalent to concatenation operator (||)
2.SUBSTR :Returns specified characters from character value starting at character position m, n characters long
(If m is negative, the count starts from the end of the character value. If n is omitted, all characters to the end of the string are returned.)
3.LENGTH :Returns the number of characters in the expression
4.INSTR :Returns the numeric position of a named string. Optionally, you can provide a position m to start searching, and the occurrence n of the string. m and n default to 1, meaning start the search at the beginning of the string and report the first occurrence.
5.LPAD/RPAD:Returns an expression left-padded to length of n characters with a character expression. Returns an expression right-padded to length of n characters with a character expression.
6.TRIM :Enables you to trim leading or trailing characters (or both) from a character string. If trim_character or trim_source is a character literal, you must enclose it in single quotation marks. This is a feature that is available in Oracle8i and later versions.
7.REPLACE:Searches a text expression for a character string and, if found, replaces it with a specified replacement string
NOTE
*********************************************************************************
1.CONCAT: Joins values together (You are limited to using two parameters with CONCAT.)
2.SUBSTR: Extracts a string of determined length
3.LENGTH: Shows the length of a string as a numeric value
4.INSTR : Finds the numeric position of a named character
5.LPAD : Returns an expression left-padded to the length of n characters with a character expression
6.RPAD : Returns an expression right-padded to the length of n characters with a character expression
7.TRIM : Trims leading or trailing characters (or both) from a character string
(If trim_character or trim_source is a character literal, you must enclose it within single quotation marks.)
*********************************************************************************
2.NUMBER FUNCTIONS : Accept numeric input and return numeric values
NUMBER FUNCTIONS--> 1.ROUND 2.TRUNC 3.MOD
1.ROUND:Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places.
(If n is negative, numbers to the left of decimal point are rounded.
2.TRUNC:Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero.
3.MOD :Returns the remainder of m divided by n.
NOTE
*********************************************************************************
DUAL Table: The DUAL table is owned by the user SYS and can be accessed by all users. It contains one column, DUMMY, and one row with the value X. The DUAL table is useful when you want to return a value only once (for example, the value of a constant, pseudocolumn, or expressionthat is not derived from a table with user data). The DUAL table is generally used for completeness of the SELECT clause syntax, because both SELECT and FROM clauses are mandatory, and several calculations do not need to select from the actual tables.
*********************************************************************************
*****NESTING FUNCTIONS*****
SELECT last_name,UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US')) FROM employees WHERE department_id = 60;
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS (hire_date, 6), 'FRIDAY'), 'fmDay, Month ddth, YYYY') "Next 6 Month Review" FROM employees ORDER BY hire_date;
*****NUMBER FUNCTIONS*****
-------------------------ROUND FUNCTIONS------------------------------
SELECT round(20.992, 2), round(20.992, 0), ROUND(20.992,-1) FROM DUAL;
-------------------------TRUNC FUNCTIONS------------------------------
SELECT TRUNC(20.992, 2), round(20.992, 0), TRUNC(20.992,-1) FROM DUAL;
SELECT TRUNC(20.392,0) FROM DUAL;
-------------------------MOD FUNCTIONS------------------------------
SELECT LAST_NAME,SALARY,MOD(SALARY,5000) FROM EMPLOYEES;
SELECT LAST_NAME,SALARY,MOD(SALARY,5000) FROM EMPLOYEES WHERE JOB_ID ='SA_REP';
3.DATE FUNCTIONS : Operate on values of the DATE data type
(All date functions return a value of the DATE data type except the MONTHS_BETWEEN function, which returns a number.)
*****DATE FUNCTIONS*****
SELECT last_name, hire_date FROM employees WHERE hire_date < '01-FEB-08';
SELECT last_name, hire_date FROM employees WHERE hire_date < '01-FEB-99';
SELECT SYSDATE FROM DUAL;
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90;
4.CONVERSION FUNCTIONS: Convert a value from one data type to another
DATA TYPE CONVERSION FUNCTIONS :1.IMPLICIT 2.EXPLICIT
1.IMPLICIT DATA TYPE CONVERSION FUNCTIONS
FROM TO
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
2.EXPLICIT DATA TYPE CONVERSION FUNCTIONS: 1.TO_CHAR 2.TO_DATE 3.TO_NUMBER
1.TO_CHAR: Converts a number or date value to a VARCHAR character string with the format model fmt
Number conversion: The nlsparams parameter specifies the following characters,
which are returned by number format elements:
• Decimal character
• Group separator
• Local currency symbol
• International currency symbol
If nlsparams or any other parameter is omitted, this function uses the default parameter values for the session.
---------------------------------------------------------NOTE---------------------------------------------------------
The format model:
• Must be enclosed with single quotation marks
• Is case-sensitive
• Can include any valid date format element
• Has an fm element to remove padded blanks or suppress leading zeros
• Is separated from the date value by a comma
TO_CHAR converts a datetime data type to a value of VARCHAR2 data type in the format specified by the format_model. A format model is a character literal that describes the format
of datetime stored in a character string. For example, the datetime format model for the string '11-Nov-2000' is 'DD-Mon-YYYY'. You can use the TO_CHAR function to convert a date
from its default format to the one that you specify.
******************Guidelines******************
• The format model must be enclosed with single quotation marks and is case-sensitive.
• The format model can include any valid date format element. But be sure to separate the date value from the format model with a comma.
• The names of days and months in the output are automatically padded with blanks.
• To remove padded blanks or to suppress leading zeros, use the fill mode fm element.
--------------------------------------------------------------------------------------------------------------------------
YYYY- FULL YEAR IN NUMBERS, YEAR- YEAR SPELLED OUT IN WORDS
MM- TWO DIGIT VALUE OF THE MONTH, MONTH- FULL NAME OF MONTH, MON- THREE LETTER ABBREVIATION OF THE MONTH
DD- NUMERIC DAY OF THE MONTH, DAY- FULL NAME OF THE DAY OF THE WEEK, DY- THREE LETTER ABBREVIATION OF THE DAY OF THE WEEK
*****Elements of the Date Format Model*****
1.Time elements format the time portion of the date:
HH24:MI:SS AM - 15:45:32 PM
2.Add character strings by enclosing them with double quotation marks:
DD "of" MONTH - 12 of OCTOBER
3.Number suffixes spell out numbers:
ddspth - fourteenth
Element
Description
AM or PM
Meridian indicator
A.M. or P.M.
Meridian indicator with periods
HH or HH12
12 hour format
HH24
24 hour format
MI
Minute (0–59)
SS
Second (0–59)
SSSSS
Seconds past midnight (0–86399)
/ . ,
Punctuation is reproduced in the result.
“of the”
Quoted string is reproduced in the result.
TH
Ordinal number (for example, DDTH for 4TH)
SP
Spelled-out number (for example, DDSP for FOUR)
SPTH or THSP
Spelled-out ordinal numbers (for example, DDSPTH for FOURTH)
******************SYNTAX******************
SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired
FROM employees
WHERE last_name = 'Higgins';
SELECT employee_id, first_name, TO_CHAR(hire_date, 'DD/MM/YY') Month_Hired FROM employees WHERE last_name = 'Grant';
SELECT employee_id, first_name, email, TO_CHAR(hire_date, 'DD/MM/YY') Month_Hired FROM employees WHERE last_name = 'Whalen';
SELECT employee_id, first_name, TO_CHAR(hire_date, 'DD/MM/YYYY') Month_Hired FROM employees WHERE last_name = 'Fay';
SELECT employee_id, first_name, TO_CHAR(hire_date, 'DD-DY/MON/YY') Month_Hired FROM employees WHERE last_name = 'Baer';
SELECT employee_id, first_name, TO_CHAR(hire_date, 'DAY-DD/MONTH-MM/YEAR-YYYY') Month_Hired FROM employees WHERE last_name = 'Baer';
*****Using the TO_CHAR Function with Dates*****
SELECT last_name, TO_CHAR(hire_date, 'fmDD Month YYYY') AS HIREDATE FROM employees;
SELECT last_name, TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') HIREDATE FROM employees;
SELECT TO_CHAR(salary, '$99,999.00') SALARY FROM employees WHERE last_name = 'Ernst';
*****Using the TO_CHAR Function with Numbers*****
Element
Result
$
Places a floating dollar sign
L
Uses the floating local currency symbol
9
Represents a number
0
Forces a zero to be displayed
.
Prints a decimal point
,
Prints a comma as a thousands indicator
*****Number Format Elements*****
If you are converting a number to the character data type, you can use the following format elements:
Element Description
Example
Result
9
Numeric position (number of 9s determine display width)
999999
1234
0
Display leading zeros
099999
001234
$
Floating dollar sign
$999999
$1234
L
Floating local currency symbol
L999999
FF1234
D
Returns the decimal character in the specified position.
The default is a period (.).
9999D99
1234.00
.
Decimal point in position specified
999999.99
1234.00
G
Returns the group separator in the specified position. You can specify multiple group
separators in a number format model.
9G999
1,234
,
Comma in position specified
999,999
1,234
MI
Minus signs to right (negative values)
999999MI
1234-
PR
Parenthesize negative numbers
999999PR
<1234>
EEEE
Scientific notation (format must specify four Es)
99.999EEEE
1.234E+03
U
Returns in the specified position the “Euro” (or other) dual currency
U9999
€1234
V
Multiply by 10 n times (n = number of 9s after V)
9999V99
123400
S
Returns the negative or positive value
S9999
-1234 or +1234
B
Display zero values as blank, not 0
B9999.99
1234.00
Using the TO_NUMBER and TO_DATE Functions:
1.Convert a character string to a number format using the
TO_NUMBER function: TO_NUMBER(char[, 'format_model'])
2.Convert a character string to a date format using the
TO_DATE function: TO_DATE(char[, 'format_model'])
3.These functions have an fx modifier. This modifier specifies the exact match for the character argument and
date format model of a TO_DATE function.
******************SYNTAX******************
SELECT last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE('May 24, 2007', 'fxMonth DD, YYYY');
--------------------------------------------------------------------------------------------------------------------------
2.TO_DATE: Converts a character string representing a date to a date value according to fmt that is specified. If fmt
is omitted, the format is DD-MON-YY.
The nlsparams parameter has the same purpose in this function as in the TO_CHAR function for date conversion.
---------------------------------------------------------NOTE---------------------------------------------------------
3.TO_NUMBER: Converts a character string containing digits to a number in the format specified by the optional format model fmt.
The nlsparams parameter has the same purpose in this function as in the TO_CHAR function for number conversion.
--------------------------------------------------------NOTE----------------------------------------------------------
5.GENERAL FUNCTIONS : 1.NVL 2.NVL2 3.NULLIF 4.COALESCE 5.CASE 6.DECODE
These functions work with any data type and pertain to the use of null values in the
expression list.
Function
Description
1.NVL
Converts a null value to an actual value
2.NVL2
If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type.
3.NULLIF
Compares two expressions and returns null if they are equal; returns the first expression if they are not equal
4.COALESCE
Returns the first non-null expression in the expression list
--------------------------------------------------------------------------------------------------------------------------
1.NVL (expr1, expr2)
In the syntax:
1.expr1 is the source value or expression that may contain a null
2.expr2 is the target value for converting the null
You can use the NVL function with any data type, but the return value is always the same as the data type of expr1.
SELECT last_name, salary,commission_pct, (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees;
SELECT last_name, salary, NVL(commission_pct, 0), (salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL FROM employees;
SELECT last_name, salary, commission_pct, (salary*12) + (salary*12*commission_pct) AN_SAL FROM employees;
2.NVL2(expr1, expr2, expr3)
In the syntax:
1.expr1 is the source value or expression that may contain a null
2.expr2 is the value that is returned if expr1 is not null
3.expr3 is the value that is returned if expr1 is null
