SQL functions Mastery : With Complete details
Useful SQL Functions on daily Work !
SQL functions : are crucial tools for data manipulation and analysis in databases. Functions like SUM and AVG are used to perform calculations and aggregations, meanwhile functions like CONCAT and SUBSTRING are used to manipulate strings, and finally functions like NOW and DATEADD are used to manipulate dates.. Mastering these built-in functions enhances your ability to perform efficient queries and complex data transformations effortlessly.
FUNCTIONS IN ORACLE :
In SQL, functions such as SUM, COUNT, and AVG are used to perform calculations and aggregations, meanwhile functions like CASE and IF are used to make decisions and manipulate data, additionally functions like CONCAT and SUBSTR are used to manipulate strings, and finally functions like TO_CHAR and TO_DATE are used to convert data types.
To perform some task as per the given input values and later it must return a value.
Oracle supports the following two types of functions are,
1. Pre-defined functions
– Use in SQL & PL/SQL
2. User-defined functions
– Use in PL/SQL
1. Pre-defined functions: In SQL, pre-defined functions such as SUM, COUNT, and AVG are used to perform calculations and aggregations, meanwhile functions like MAX and MIN are used to find the maximum and minimum values, additionally functions like UPPER and LOWER are used to manipulate strings, and finally functions like LENGTH and TRIM are used to manipulate string lengths and remove unwanted characters.
These are called as “Built In Functions” in oracle.it again two types
i) Single row functions
ii) Multiple row functions
i) Single row functions:
These functions are always return a single value only.
> Numeric functions
> Character / String functions
> Date functions
> Null functions (NVL() & NVL2())
> Conversion functions
> Analytical functions ( In subquery )
How to call a Function:
syntax:
SELECT <FNAME>(VALUE / (S)) FROM DUAL;
What is Dual:
In SQL, the DUAL table is a special table that can be used to perform calculations and aggregations, meanwhile it can also be used to test and validate queries, additionally it can be used to generate a row of data for testing purposes, and finally it can be used to simplify complex queries and improve performance.
– it is a system defined table / pre-define table in oracle.
– it is used to test a function functionalities in database.
– it is also called as “dummy table” in oracle database.
– this table is having only one row and one column.
How to view the structure of dual table:
syntax: DESC <TABLE NAME>;
EX:SQL> DESC DUAL;
Name Null? Type
DUMMY VARCHAR2(1)
How to view data in dual table:
Syntax: SELECT * FROM <TABLE NAME>;
EX:SQL> SELECT * FROM DUAL;
D ————-> single column
X ————> single row
Numeric functions:
In SQL, numeric functions such as ABS, CEIL, and FLOOR are used to perform mathematical operations, meanwhile functions like MOD and POWER are used to calculate remainders and exponents, additionally functions like ROUND and TRUNC are used to round and truncate numbers, and finally functions like SQRT and EXP are used to calculate square roots and exponents.
ABS(): To convert (-ve) sign values into (+) sign values.
syntax: abs(n)
Ex: SQL> SELECT ABS(-12) FROM DUAL;
ABS(-12)
12
SQL> SELECT ENAME,SAL,COMM,ABS(COMM-SAL) AS TOTAL_SALARY FROM EMP;
CEIL():It returns a value which is equals to / greater than to the given expression.
syntax: ceil(n)
Ex: SQL> SELECT CEIL(9.2) AS RESULT FROM DUAL;
RESULT
10
SQL> SELECT CEIL(9.0) AS RESULT FROM DUAL;
RESULT : 9
FLOOR(): It returns a value which is equals to / less than to the given expression.
syntax: floor(n)
Ex: SQL> SELECT FLOOR(9.8) AS RESULT FROM DUAL;
RESULT : 9
SQL> SELECT FLOOR(9.0) AS RESULT FROM DUAL;
RESULT: 9
MOD():It return the remainder value from the given expression.
syntax: mod(m,n)
Ex: SQL> SELECT MOD(10,2) FROM DUAL;
MOD(10,2)
0
SQL> SELECT * FROM EMP WHERE MOD(EMPNO,2)=0; (Even ID’s)
SQL> SELECT * FROM EMP WHERE MOD(EMPNO,2)=1; (Odd ID’s)
POWER():It return the power of the given expression.
syntax: power(m,n)
Ex: SQL> SELECT POWER(2,3) FROM DUAL;
POWER(2,3) = 8
ROUND(): Round functions such as ROUND and TRUNC are used to round and truncate numbers, meanwhile the ROUND function is used to round a number to a specified precision,
It return the nearest value of the given expression based on 0.5 value.
> if an expression
value > 0.5 then add “1” to main value
value = 0.5 than add “1” to main value
value < 0.5 then add “0” to main value
syntax: round(expression [, decimal places])
EX:SQL> SELECT ROUND(56.3) FROM DUAL;
Solution:56.3 ———–> 0.3 < 0.5 ——-> add “0”
0
====
56
=====
SQL> SELECT ROUND(56.5) FROM DUAL;
Solution:
56.5 ———–> 0.5 = 0.5 ——-> add “1”
1
====
57
=====
SQL> SELECT ROUND(56.8) FROM DUAL;
Solution:
56.8 ———–> 0.8 > 0.5 ——-> add “1”
1
====
57
=====
SQL> SELECT ROUND(56.351,2) FROM DUAL;
Solution:
56.351 ———–> 56.35 —–> 0.1 < 0.5 ——-> add “0”
0
56.35
=====
SQL> SELECT ROUND(56.358,2) FROM DUAL;
Solution:
56.358 ———–> 56.35 —–> 0.8 > 0.5 ——-> add “1”
1
=====
56.36
======
TRUNC():
TRUNC function is used to truncate a number to a specified precision, meanwhile it is used to remove decimal places from a number, additionally it is used to simplify complex calculations, and finally it is used to improve performance and reduce data storage requirements.
It return an exact value from the given expression without consider 0.5 value.
syntax:
trunc(expression [, decimal places ])
EX: SQL> SELECT TRUNC(56.8) FROM DUAL;
TRUNC(56.8)
56
SQL> SELECT TRUNC(56.879,2) FROM DUAL;
TRUNC(56.879,2)
56.87
Character / String functions:
LENGTH():
Length functions such as LENGTH and CHAR_LENGTH are used to calculate the length of a string, meanwhile the LENGTH function is used to calculate the length of a string in bytes, additionally the CHAR_LENGTH function is used to calculate the length of a string in characters, and finally both functions are used to validate and manipulate string data.
It return the length of the given string expression.
syntax: length(string)
Ex:
SQL> SELECT LENGTH(‘HELLO’) FROM DUAL;——————-5
SQL> SELECT LENGTH(‘WEL COME’) FROM DUAL;————8
EX:
SQL> SELECT ENAME,LENGTH(ENAME) FROM EMP;
SQL> SELECT * FROM EMP WHERE LENGTH(ENAME)=5;
SQL> SELECT * FROM EMP WHERE LENGTH(ENAME)<5;
SQL> SELECT * FROM EMP WHERE LENGTH(ENAME)>5;
LOWER():
LOWER function is used to convert a string to lowercase, meanwhile it is used to perform case-insensitive searches, additionally it is used to simplify complex string comparisons, and finally it is used to improve data consistency and reduce errors.
To convert upper case characters into lower case characters.
syntax: lower(string)
Ex: SQL> SELECT LOWER(‘HELLO’) FROM DUAL;——————hello
EX:SQL> UPDATE EMP SET ENAME=LOWER(ENAME) WHERE JOB=’MANAGER’;
SQL> UPDATE EMP SET ENAME=LOWER(ENAME);
UPPER():
UPPER function is used to convert a string to uppercase, meanwhile it is used to perform case-insensitive searches, additionally it is used to simplify complex string comparisons, and finally it is used to improve data consistency and reduce errors.
To convert lower case characters into upper case characters.
syntax: upper(string)
Ex: SQL> UPDATE EMP SET ENAME=UPPER(ENAME);
INITCAP():
INITCAP function is used to initialize a string to proper case, meanwhile it is used to capitalize the first letter of each word, additionally it is used to simplify complex string manipulations, and finally it is used to improve data readability and consistency.”
To make the initial character is capital.
syntax: initcap(string)
Ex: SQL> SELECT INITCAP(‘yuvin’) FROM DUAL;———————-Yuvin
SQL> SELECT ENAME,INITCAP(ENAME) FROM EMP;
LTRIM():
LTRIM function is used to remove leading and trailing characters from a string, meanwhile it is used to simplify complex string manipulations, additionally it is used to improve data quality and reduce errors, and finally it is used to enhance data processing and analysis.
To remove unwanted character / (s) from the given string expression on left side.
syntax: ltrim(string,'<trimming character /(s)’)
EX:
SQL> SELECT LTRIM(‘XXXXSMITH’,’X’) FROM DUAL;—————-SMITH
SQL> SELECT LTRIM(‘XYZSMITH’,’XYZ’) FROM DUAL;————–SMITH
RTRIM():
RTRIM function is used to remove trailing characters from a string, meanwhile it is used to simplify complex string manipulations, additionally it is used to improve data quality and reduce errors, and finally it is used to enhance data processing and analysis
To remove unwanted character / (s) from the given string expression on right side.
syntax: rtrim(string,'<trimming character /(s)’)
EX:
SQL> SELECT RTRIM(‘SMITHXXXX’,’X’) FROM DUAL;—————-SMITH
SQL> SELECT RTRIM(‘SMITHXYZ’,’XYZ’) FROM DUAL;————–SMITH
TRIM():To remove unwanted character from the both sides of a given string expression.
syntax: trim(<trimming character> from STRING)
EX:SQL> SELECT TRIM(‘X’ FROM ‘XXXSMITHXXXXX’) FROM DUAL;————–SMITH
EX: SQL> SELECT TRIM(‘XY’ FROM ‘XXXSMITHYYY’) FROM DUAL;
ERROR at line 1:ORA-30001: trim set should have only one character
REPLACE():
REPLACE function is used to replace a string with another string, meanwhile it is used to simplify complex string manipulations, additionally it is used to improve data quality and reduce errors, and finally it is used to enhance data processing and analysis
– to replace a single character with group of characters.
(or)
– to replace group of characters with a single character.
syntax:
replace(string,'<old char’s>’,'<new char’s>’)
EX:
SQL> SELECT REPLACE(‘HELLO’,’ELL’,’X’) FROM DUAL;————-HXO
SQL> SELECT REPLACE(‘JACK AND JUE’,’J’,’BL’) FROM DUAL;———–BLACK AND BLUE
TRANSLATE():To translate a single character by a single character.
syntax: Translate(string,'<old char’s>’,'<new char’s>’)
Ex:
SQL> SELECT TRANSLATE(‘HELLO’,’ELO’,’XYZ’) FROM DUAL;————HXYYZ
SQL> SELECT TRANSLATE(‘HELLO’,’HO’,’AB’) FROM DUAL;—————AELLB
SQL> SELECT TRANSLATE(‘HELLO’,’EL’,’XYZ’)FROM DUAL;————–HXYYO
LPAD():
LPAD function is used to left-pad a string with a specified character, meanwhile it is used to simplify complex string manipulations, additionally it is used to improve data quality and reduce errors, and finally it is used to enhance data processing and analysis.
To fill a specific character on left side of the given string if the length of string is less than to user defined length.
syntax: lpad(string,<user defined length>,[<specific character>])
Ex:
SQL> SELECT LPAD(‘HELLO’,10) FROM DUAL;
HELLO
SQL> SELECT LPAD(‘HELLO’,10,’@’) FROM DUAL;
@@@@@HELLO
RPAD():
RPAD function is used to right-pad a string with a specified character, meanwhile it is used to simplify complex string manipulations, additionally it is used to improve data quality and reduce errors, and finally it is used to enhance data processing and analysis.
To fill a specific character on right side of the given string if the length of string is
less than to user defined length.
syntax: rpad(string,<user defined length>,[<specific character>])
Ex: SQL> SELECT RPAD(‘HELLO’,10) FROM DUAL;
HELLO
SQL> SELECT RPAD(‘HELLO’,10,’@’) FROM DUAL;
HELLO@@@@@
SUBSTR(): It return the required substring from the given string expression, meanwhile it is used to simplify complex string manipulations, additionally it is used to improve data quality and reduce errors, and finally it is used to enhance data processing and analysis.
syntax: substr(string,<starting position of character>,<length of the chararcters>)
EX: -7 -6-5-4 -3 -2 -1 (right to left)
‘ W E L C O M E ‘
1 2 3 4 5 6 7 ( left to right)
SQL> SELECT SUBSTR(‘WELCOME’,3,2) FROM DUAL;————LC
SQL> SELECT SUBSTR(‘WELCOME’,6,1) FROM DUAL;———– M
SQL> SELECT SUBSTR(‘WELCOME’,-3,2) FROM DUAL;———-OM
SQL> SELECT SUBSTR(‘WELCOME’,-5,4) FROM DUAL;———–LCOM
INSTR():
It return the occurrence position of a specific character from the given string
expression.
syntax: instr(string,<specific character>,<starting position of character>,<occurrence position of character>)
Expression:
-13-12-11-10-9-8-7-6 -5 -4 -3 -2 -1 (right to left)
‘W E L C O M E H E L L O’
1 2 3 4 5 6 7 8 9 10 11 12 13 (left to right)
Note: counting characters from left to right / right to left then the position of character is always fixed position.
EX:
SQL> SELECT INSTR(‘WELCOME HELLO’,’O’) FROM DUAL;———————5
SQL> SELECT INSTR(‘WELCOME HELLO’,’O’,1,1) FROM DUAL;—————5
SQL> SELECT INSTR(‘WELCOME HELLO’,’O’,1,2) FROM DUAL;—————13
SQL> SELECT INSTR(‘WELCOME HELLO’,’E’,7,2) FROM DUAL;————-10
SQL> SELECT INSTR(‘WELCOME HELLO’,’L’,11,3) FROM DUAL;————0
SQL> SELECT INSTR(‘WELCOME HELLO’,’O’,-1,1) FROM DUAL;————13
SQL> SELECT INSTR(‘WELCOME HELLO’,’L’,-4,1) FROM DUAL;————3
SQL> SELECT INSTR(‘WELCOME HELLO’,’E’,-7,3) FROM DUAL;————0
DATE FUNCTIONS:
SYSDATE: It return the current system date.
syntax: sysdate
EX: SQL> SELECT SYSDATE FROM DUAL;
SYSDATE
SQL> SELECT SYSDATE+10 FROM DUAL;
SYSDATE+1
07-JUN-24
SQL> SELECT SYSDATE-10 FROM DUAL;
SYSDATE-1
——————-
18-MAY-24
ADD_MONTHS():To add / subtract no.of months to a date / from a date.
syntax: add_months(date,<no.of months>)
EX: SQL> SELECT SYSDATE AS CURRENT_DATE,
2 ADD_MONTHS(SYSDATE,3) AS NEW_DATE FROM DUAL;
CURRENT_DATE NEW_DATE
28-MAY-24 28-AUG-24
SQL> SELECT SYSDATE AS CURRENT_DATE,
2 ADD_MONTHS(SYSDATE,-3) AS OLD_DATE FROM DUAL;
CURRENT_DATE OLD_DATE
28-MAY-24 28-FEB-24
EX:
SQL> CREATE TABLE PRODUCT(PCODE NUMBER(3),PNAME VARCHAR2(10),MFG_DATE DATE,EXP_DATE DATE);
SQL> INSERT INTO PRODUCT(PCODE,PNAME,MFG_DATE) VALUES(101,’P1′,’12-JUN-2022′);
SQL> INSERT INTO PRODUCT(PCODE,PNAME,MFG_DATE) VALUES(102,’P2′,’08-MAR-2023′);
SQL> SELECT * FROM PRODUCT; ( before updating )
SQL> UPDATE PRODUCT SET EXP_DATE=ADD_MONTHS(MFG_DATE,24);
SQL> SELECT * FROM PRODUCT; (after updating )
LAST_DAY():It return the last date from the given month in date expression.
syntax: last_day(date)
Ex: SQL> SELECT LAST_DAY(SYSDATE) FROM DUAL;
31-MAY-24
SQL> SELECT LAST_DAY(’03-JUN-22′) FROM DUAL;
LAST_DAY(
——————–
30-JUN-22
MONTHS_BETWEEN():It return the no.of months between the given two dates expressions.
syntax: months_between(date1,date2)
EX:
SQL> SELECT MONTHS_BETWEEN(’05-JUN-23′,’05-JUN-24′) FROM DUAL;————-> -12
– Here , date1 is always greater than to date2 otherwise it returns (-ve) sign value.
SQL> SELECT MONTHS_BETWEEN(’05-JUN-24′,’05-JUN-23′) FROM DUAL;—————–> 12
Conversion functions:
i) TO_CHAR()
ii) TO_DATE()
i) TO_CHAR():To convert date type to character type and also display date information in different formats.
syntax: to_char(sysdate,'<interval>’)
Year Formats:
YYYY – return year in four digits format
YY – return year in last two digits format
YEAR – return in character format i.e Twenty Twenty-Four
CC – Centuary 21
AD / BC – AD Year / BC Year
Ex:SQL> SELECT TO_CHAR(SYSDATE,’YYYY YY YEAR CC BC’) FROM DUAL;
RESULT:
2024 24 TWENTY TWENTY-FOUR 21 AD
Month Format:
MM – Month In Number Format
MON – First Three Char’s From Month Spelling
MONTH – Full Name Of The Month
EX: SQL> SELECT TO_CHAR(SYSDATE,’MM MON MONTH’) FROM DUAL;
OUTPUT
05 MAY MAY
Day Formats:
DDD – Day Of The Year.
DD – Day Of The Month.
D – Day Of The Week
Sun – 1
Mon – 2
Tue – 3
Wen – 4
Thu – 5
Fri – 6
Sat – 7
DAY – Full Name Of The Day
DY – First Three Char’s Of Day Spelling
EX:SQL> SELECT TO_CHAR(SYSDATE,’DDD DD D DY DAY’) FROM DUAL;
OUTPUT
149 28 3 TUE TUESDAY
Quarter Format:
Q – One Digit Quater Of The Year
1 – Jan – Mar
2 – Apr – Jun
3 – Jul – Sep
4 – Oct – Dec
EX: SQL> SELECT TO_CHAR(SYSDATE,’Q’) FROM DUAL;
T
–
2
Week Format:
WW – Week Of The Year
W – Week Of Month
EX:
SQL> SELECT TO_CHAR(SYSDATE,’W WW’) FROM DUAL;
TO_C
—-
5 22
Time Format:
HH – Hour Part In 12hrs Format
HH24 – Hour Part In 24hrs Fromat
MI – Minute Part
SS – Seconds Part
AM / PM – AM Time (Or) PM Time
EX: SQL> SELECT TO_CHAR(SYSDATE,’HH HH24 MI SS PM’) FROM DUAL;
TO_CHAR(SYSDAT
————–
11 11 31 33 AM
Ex Want to list out the employees who are joined in 1981 by using to_char()?
SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’YYYY’)=’1981′;
(OR)
SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’YY’)=’81’;
(OR)
SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’YEAR’)=’NINETEEN EIGHTY-ONE’;
Ex:Want to list out the employees details who are joined in 1980,1982,1983 by using to_char()?
SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’YY’)=’80’
OR TO_CHAR(HIREDATE,’YY’)=’82’
OR TO_CHAR(HIREDATE,’YY’)=’83’;
(OR)
SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’YY’) IN(’80’,’82’,’83’);
Ex: Want to display list of employees who are joined in “DECEMBER” month by using to_char()?
SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’MM’)=’12’;
Ex: Want to display list of employees who are joined in the month of “DECEMBER” in 1981?
SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’MM’)=’12’ AND TO_CHAR(HIREDATE,’YYYY’)=’1981′;
(OR)
SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’MMYYYY’)=’121981′;
Ex: want to display employees who are joined in 2nd quarter of the year?
SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’Q’)=’2′;
Ex: Want to display employees who are joined in 3rd quarter of 1981?
SQL> SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,’QYYYY’)=’31981′;
II) TO_DATE():To convert char type to oracle default date type.
syntax: to_date(‘STRING’)
Ex: SQL> SELECT TO_DATE(’23/AUGUST/2022′) FROM DUAL;
23-AUG-22
SQL> SELECT TO_DATE(’23/AUGUST/2022′)+5 FROM DUAL;
28-AUG-22
SQL> SELECT TO_DATE(’23/AUGUST/2022′)-5 FROM DUAL;
18-AUG-22
ii) Multiple row functions: These functions are also called as “aggregative / grouping ” functions in database.
i) SUM():It return total value.
Ex: SQL> SELECT SUM(SAL) FROM EMP;
SQL> SELECT SUM(SAL) FROM EMP WHERE DEPTNO=30;
ii) AVG():It return the average of the given total value by no.of items.
Ex: SQL> SELECT AVG(SAL) FROM EMP;
SQL> SELECT AVG(SAL) FROM EMP WHERE DEPTNO=30;
iii) MIN():It return minimum value.
Ex:
SQL> SELECT MIN(SAL) FROM EMP;
SQL> SELECT MIN(HIREDATE) FROM EMP;
SQL> SELECT MIN(SAL) FROM EMP WHERE JOB=’SALESMAN’;
iv) MAX():It return maximum value.
Ex: SQL> SELECT MAX(SAL) FROM EMP;
v) COUNT(): COUNT(*) :Counting all rows including duplicates and NULLS in a table.
Ex: SQL> SELECT COUNT(*) FROM EMP; ——— 14
COUNT( column name):Counting all rows including duplicates but not NULLS in a table.
Ex: SQL> SELECT COUNT(MGR) FROM EMP;
COUNT(distinct <column name>):
Counting unique values from a table (i.e no duplicate & no NULLs)
Ex:
SQL> SELECT COUNT(DISTINCT MGR)FROM EMP;
If you want to learn more on SQL operators then please visit our SQL Operator section
If you want to SQL practice then please visit
