Single row function accepts one or more arguments and returns one value for each row.
A single row functions can be used in SELECT, WHERE, and ORDER BY
Also single functions can be nested
Types of single row functions
1- Number functions: accept numeric input and return numeric values.
2- Character functions: accept character input
3- Date functions :accept date values
4- Data type conversion: convert a value from one data type to another.
5- General functions: improve the power of your SQL statement. this category includes an important function to replace with an actual value If a column is NULL, and another powerful function to run queries based on conditional, IF –THEN-ELSE logic.
---------------------------------------------------------------------------------------------------------------------
Single row functions
---------------------------------------------------------------------------------------------------------------------
Single row functions
S Single row function accepts one or more arguments and returns one value for each row.
A single row functions can be used in SELECT, WHERE, and ORDER BY
Al so single functions can be nested
T types of single row functions
1- 1-Number functions: accept numeric input and return numeric values.
2- 2-Character functions: accept character input
3- 3-Date functions :accept date values
4- 4-Data type conversion: convert a value from one data type to another.
5- 5-General functions: improve the power of your SQL statement. this category includes an important function to replace with an actual value If a column is NULL, and another powerful function to run queries based on conditional, IF –THEN-ELSE logic
----------------------------------------------------------------------------------------------------------------
TO_CHAR string
TO_CHAR function converts a number or date to AVARCHAR2 in a format that you specify.
TO_CHAR with date
You can use the TO_CHAR function to convert a date from the default data format to a format that you identify.
The default format is DD-MON-YY .if you need to change the default format then you can use the TO_CHAR function to convert a date into a new format.
For example
Select empno,ename,to_char(hiredate,’dd/mm/yyyy’) “JOIN DATE”
FROM EMP;
EMPNO ENAME JOIN DATE
---------- ---------- ----------
7369 SMITH 17/12/1980
7499 ALLEN 20/02/1981
7521 WARD 22/02/1981
7566 JONES 02/04/1981
7654 MARTIN 28/09/1981
7698 BLAKE 01/05/1981
7782 CLARK 09/06/1981
7788 SCOTT 19/04/1987
7839 KING 17/11/1981
7844 TURNER 08/09/1981
7876 ADAMS 23/05/1987
EMPNO ENAME JOIN DATE
---------- ---------- ----------
7900 JAMES 03/12/1981
7902 FORD 03/12/1981
7934 MILLER 23/01/1982
Select empno,ename,to_char(hiredate,’DD MONTH YYYY’) “JOIN DATE”
FROM EMP;
EMPNO ENAME JOIN DATE
-------- ---------- --------------------
7369 SMITH 17 DECEMBER 1980
7499 ALLEN 20 FEBRUARY 1981
7521 WARD 22 FEBRUARY 1981
7566 JONES 02 APRIL 1981
7654 MARTIN 28 SEPTEMBER 1981
7698 BLAKE 01 MAY 1981
7782 CLARK 09 JUNE 1981
7788 SCOTT 19 APRIL 1987
7839 KING 17 NOVEMBER 1981
7844 TURNER 08 SEPTEMBER 1981
7876 ADAMS 23 MAY 1987
EMPNO ENAME JOIN DATE
-------- ---------- --------------------
7900 JAMES 03 DECEMBER 1981
7902 FORD 03 DECEMBER 1981
7934 MILLER 23 JANUARY 1982
ALSO THE SEE BELOW EXAMPLE
Select empno,ename,to_char(hiredate,’FMDAY , DD MONTH YYYY’) “JOIN DATE”
FROM EMP;
EMPNO ENAME JOIN DATE
------- ---------- ------------------------------
7369 SMITH WEDNESDAY , 17 DECEMBER 1980
7499 ALLEN FRIDAY , 20 FEBRUARY 1981
7521 WARD SUNDAY , 22 FEBRUARY 1981
7566 JONES THURSDAY , 2 APRIL 1981
7654 MARTIN MONDAY , 28 SEPTEMBER 1981
7698 BLAKE FRIDAY , 1 MAY 1981
7782 CLARK TUESDAY , 9 JUNE 1981
7788 SCOTT SUNDAY , 19 APRIL 1987
7839 KING TUESDAY , 17 NOVEMBER 1981
7844 TURNER TUESDAY , 8 SEPTEMBER 1981
7876 ADAMS SATURDAY , 23 MAY 1987
EMPNO ENAME JOIN DATE
------- ---------- ------------------------------
7900 JAMES THURSDAY , 3 DECEMBER 1981
7902 FORD THURSDAY , 3 DECEMBER 1981
7934 MILLER SATURDAY , 23 JANUARY 1982
TO_CHAR with number
You can use the TO_CHAR function to convert other datatype values to characters.
For example
To display employee salaries with $ (dollar sign)
Type
Select empno,ename,TO_CHAR(sal,’$99,999’)
From emp
Where empno=7499
EMPNO ENAME TO_CHAR(
------- ---------- --------
7499 ALLEN $1,600
------------------------------------------------------------------------------------------------------------------
TO_NUMBER
To convert a character string to a number ,you use the TO_NUMBER function
For example
Select empno,ename,sal
From emp
Where sal > TO_NUMBER (‘$1,600’,’$9,999’);
EMPNO ENAME SAL
------ ---------- ----------
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
-----------------------------------------------------------------------------------------------------------------
TO_DATE function
Used to convert a character string to a date format.
For example
Assume that you have a character string on behalf of a date value. With format February 20, 1981. If you want compare this character string to a date value .you will receive a error
Then you must used TO_DATE function to convert the character string into a date value.
TYPE
SELECT EMPNO,ENAME,HIREDATE
FROM EMP
WHERE HIREDATE = TO_DATE(‘February 20, 1981’,’MONTH DD,YYYY’);
EMPNO ENAME HIREDATE
------ ---------- ---------
7499 ALLEN 20-FEB-81
See below links
SYSDATE Function
DATE Arithmetic
Common Date Functions
-----------------------------------------------------------------------------------------------
LPAD Function
CLARK CLADAMS AD
CLARK 0ADAMS 5
CLARK 80.55ADAMS 36.16
CLARK 80.54ADAMS 36.16
CLARK No commissionADAMS No commission
DATE Arithmetic
Common Date Functions
-----------------------------------------------------------------------------------------------
LPAD Function
L By using the LPAD function you can place a character or characters on the left of a character value.
In the LPAD function syntax, you specify the name of the column that you want to pad
For example
If you want pad employee names with percentage ‘%’
type
select ename,LPAD(ename,8,’%’)
from emp;
ENAME LPAD(ENA
---------- --------
SMITH %%%SMITH
ALLEN %%%ALLEN
WARD %%%%WARD
JONES %%%JONES
MARTIN %%MARTIN
BLAKE %%%BLAKE
CLARK %%%CLARK
SCOTT %%%SCOTT
KING %%%%KING
TURNER %%TURNER
ADAMS %%%ADAMS
ENAME LPAD(ENA
---------- --------
JAMES %%%JAMES
FORD %%%%FORD
MILLER %%MILLER
------------------------------------------------------------------------------------------------------------------
RPAD function
------------------------------------------------------------------------------------------------------------------
RPAD function
R RPAD function it use a same Logic for LPAD the different is RPAD function pads the character value on the right
select ename,RPAD(ename,8,’%’)
from emp;
ENAME RPAD(ENA
---------- --------
SMITH SMITH%%%
ALLEN ALLEN%%%
WARD WARD%%%%
JONES JONES%%%
MARTIN MARTIN%%
BLAKE BLAKE%%%
CLARK CLARK %%%
SCOTT SCOTT%%%
KING KING%%%%
TURNER TURNER%%
ADAMS ADAMS %%%
ENAME RPAD(ENA
---------- --------
JAMES JAMES%%%
FORD FORD%%%%
MILLER MILLER%%
-----------------------------------------------------------------------------------------------------------------
Length Function
Length Function
You used length function to know the number of characters in a string (as a numeric value).
For example
To show the number of character for each job
SQL> select job,length(job)
from emp;
JOB LENGTH(JOB)
--------- -----------
CLERK 5
SALESMAN 8
SALESMAN 8
MANAGER 7
SALESMAN 8
MANAGER 7
MANAGER 7
ANALYST 7
PRESIDENT 9
SALESMAN 8
CLERK 5
-----------------------------------------------------------------------------------------------------------------
SUBSTR and INSTR functions
That function used to display information about a value
SUBSTR FUNCTION
You can take out a string of a determined length using the SUBSTR function.
Using the SUBSTR function, you specify a character string followed by two numbers. The first number represents the character position from which to begin extracting, and the second number represents the number of characters that you want to extract.
For example
Assume that you want cut the first two letters of an employee’s last name
Select ename,SUBSTER(ENAME,1,2)
FROM EMP;
ENAME SU
---------- --
SMITH SM
ALLEN AL
WARD WA
JONES JO
MARTIN MA
BLAKE BL
SCOTT SC
KING KI
TURNER TU
ENAME SU
---------- --
JAMES JA
FORD FO
MILLER MI
INSTR function
By using INSTR function you can find the numeric position of a named character
Also you specify a character string followed by the character for which you want the numeric position
The function returns a numeric value instead of the placement of the first happening of the individual letter in the string.
For example
Assume that you want know the position of the first S in each employee name.
Then type
SELECT ENAME,INSTR(ENAME,’S’)
FROM EMP;
ENAME INSTR(ENAME,'S')
---------- ----------------
SMITH 1
ALLEN 0
WARD 0
JONES 5
MARTIN 0
BLAKE 0
SCOTT 1
KING 0
TURNER 0
ENAME INSTR(ENAME,'S')
---------- ----------------
JAMES 5
FORD 0
MILLER 0
------------------------------------------------------------------------------------------------------------------
ROUND function
ROUND function
R By using the round function you can round a number to a specified number of decimal
The ROUND function .Accept numeric input and returns numeric values.
If you don’t specify a number of decimal places, or if you specify a zero for the number of decimal places, then the values is rounded to the left of the decimal
For example
If you want to calculate an employee’s daily salary.
Type
SELECT ENAME,ROUND (SAL*12/365,2)
FROM EMP;
ENAME ROUND(SAL*12/365,2)
---------- -------------------
SMITH 26.3
ALLEN 52.6
WARD 41.1
JONES 97.81
MARTIN 41.1
BLAKE 93.7
SCOTT 98.63
KING 164.38
TURNER 49.32
ENAME ROUND(SAL*12/365,2)
---------- -------------------
JAMES 31.23
FORD 98.63
MILLER 42.74
Note : that you can round the result to the nearest integer by not specifying a number of decimal places in the ROUND function.
-----------------------------------------------------------------------------------------------------------------
TRUNCATE function
TRUNCATE function
SQL Truncate
The SQL TRUNCATE function .Accept numeric input and returns numeric values.
You specify the TRUNC function keyword followed by the column or expression that you want to truncate and the number of decimal places to which you want to truncate in the result.
If you don’t specify a number of decimal places, or if you specify a zero for the number of decimal places, then tha value is truncated to the integer. If you specify a negative number of decimal places, then values to the left of decimal place are truncated to zero.
For example
Assume that you want to calculate an employee daialy salary as sal*12/365 and truncate the result to two decimal places.
Type
SELECT ENAME,TRUNC (SAL*12/365,2)
FROM EMP;
ENAME TRUNC(SAL*12/365,2)
---------- -------------------
SMITH 26.3
ALLEN 52.6
WARD 41.09
JONES 97.8
MARTIN 41.09
BLAKE 93.69
SCOTT 98.63
KING 164.38
TURNER 49.31
ENAME TRUNC(SAL*12/365,2)
---------- -------------------
JAMES 31.23
FORD 98.63
MILLER 42.73
The result produces the rest of the first value divided by the second value.
For example assume that you want calculate the rest of salary divided by commission
For employee number 7654
Type
Select empno,ename,sal,comm,mod(sal,comm)
From emp
Where empno= 7654 ;
EMPNO ENAME SAL COMM MOD(SAL,COMM)
------- ---------- ---------- ---------- -------------
7654 MARTIN 1250 1400 1250
If you see the result you will find that the function returns the same salary value
Because the comm value is larger than the salary value also if comm. Equal zero the result it will be same first value(in our example sal)
Please check the below example for clarification
Select empno, ename, sal, comm, MOD (SAL, COMM)
FROM EMP
WHERE JOB =’SALESMAN’;
EMPNO ENAME SAL COMM MOD(SAL,COMM)
---------- ---------- ---------- ---------- -------------
7499 ALLEN 1600 300 100
7521 WARD 1250 500 250
7654 MARTIN 1250 1400 1250
7844 TURNER 1500 0 1500
---------------------------------------------------------------------------------------------
MOD function
MOD function
The result produces the rest of the first value divided by the second value.
For example assume that you want calculate the rest of salary divided by commission
For employee number 7654
Type
Select empno,ename,sal,comm,mod(sal,comm)
From emp
Where empno= 7654 ;
EMPNO ENAME SAL COMM MOD(SAL,COMM)
------- ---------- ---------- ---------- -------------
7654 MARTIN 1250 1400 1250
----------------------------------------------------------------------------------------------------------------
L You used length function to know the number of characters in a string (as a numeric value).
For example
To show the number of character for each job
SQL> select job,length(job)
from emp;
JOB LENGTH(JOB)
--------- -----------
CLERK 5
SALESMAN 8
SALESMAN 8
MANAGER 7
SALESMAN 8
MANAGER 7
MANAGER 7
ANALYST 7
PRESIDENT 9
SALESMAN 8
CLERK 5
JOB LENGTH(JOB)
--------- -----------
CLERK 5
ANALYST 7
CLERK 5
------------------------------------------------------------------------------------------------------------------
NVL function
NVL SQL used to convert a null value to an actual value.
To convert null values in a numeric, character, or date . you must specify a return value that is a number , character ,or date
For example
If you want to calculate annual compensation for all employees
Type
SELECT EMPNO,ENAME, SAL,COMM,(SAL*12)+NVL(COMM,0) Annual
FROM EMP;
EMPNO ENAME SAL COMM ANNUAL
------ ---------- ---------- ---------- ----------
7369 SMITH 800 9600
7499 ALLEN 1600 300 19500
7521 WARD 1250 500 15500
7566 JONES 2975 35700
7654 MARTIN 1250 1400 16400
7698 BLAKE 2850 34200
7782 CLARK 2450 29400
7788 SCOTT 3000 36000
7839 KING 5000 60000
7844 TURNER 1500 0 18000
7876 ADAMS 1100 13200
------------------------------------------------------------------------------------------------------------------
DECODE function
DECODE IN SQL
DECODE IN SQL
It a single – row function it do the work of IF –THEN-ELSE . the DECODE function compares a column or expression to each search value.
DECODE Syntax.
DECOD (COLUMN_NAME, SEARCH1, RESULT1, SEARCH2, RESULT2)
FOR EXAMPLE
if you want calculate the salary increases by 10% for employees who working in department 20 using THE DECODE
type
SELECT EMPNO, ENAME,SAL,DEPTNO,DECODE(deptno,10,sal*1.1) “INCREMENT”
From emp;
EMPNO ENAME SAL DEPTNO INCREMENT
------ ---------- ---------- ---------- ----------
7369 SMITH 800 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 JONES 2975 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7782 CLARK 2450 10 2695
7788 SCOTT 3000 20
7839 KING 5000 10 5500
7844 TURNER 1500 30
7876 ADAMS 1100 20
EMPNO ENAME SAL DEPTNO INCREMENT
------ ---------- ---------- ---------- ----------
7900 JAMES 950 30
7902 FORD 3000 20
7934 MILLER 1300 10 1430
If you check the result you will find that only employees who working in department 10 get increment.
------------------------------------------------------------------------------------------------------------------
NESTING Function
It used to create complex and flexible statements that retrieve exactly the data that you need. NESTING function it functions within functions.
For example
Assume that you want display how earn commission and who’s not by ‘NO COMMISSION’
TYPE
SELECT ENAME, NVL (TO_CHAR (COMM),’No commission’) earn_comm
From emp;
ENAME EARN_COMM
---------- ---------------
SMITH No commission
ALLEN 300
WARD 500
JONES No commission
MARTIN 1400
BLAKE No commission
SCOTT No commission
KING No commission
TURNER 0
ENAME EARN_COMM
---------- ---------------
JAMES No commission
FORD No commission
MILLER No commission
D
Posts
No comments:
Post a Comment