SQL
FUNCTIONS
SINGLE ROW MULTIPLE ROW
FUNCTION FUNCTIONS
• Operates on character
STRING datatype
• Operates on integer datatype
NUMERIC
DATE AND • Operates on date&time datatype
TIME
Single Row Function
It operates on single value to return single value as
output.
One or more arguments are passed but returns only
one result
String
1. Trim ( ) : It removes spaces from beginning and
ending of the string.
No. of arguments: 1
Argument: String Return: String
Syntax: SELECT TRIM ( str );
Example: SELECT TRIM (“ name “);
O/p: TRIM ( name )
name
2. Ltrim ( ) : It removes only space from
beginning of the string
No. of arguments: 1
Argument: String Return: String
Syntax: SELECT LTRIM (str);
Example: SELECT LTRIM (“ BK”);
O/p: LTRIM( BK)
BK
3. Rtrim ( ) : It removes only space from ending of
the string
No. of arguments: 1
Argument: String Return: String
Syntax: SELECT RTRIM (str);
Example: SELECT RTRIM (“BK ”);
O/p: RTRIM(BK )
BK
4. Instr ( ) : It searches one string in another string.
No. of arguments: 2
Argument: String, String Return:String
Syntax: SELECT INSTR (str1,str2);
Example: SELECT INSTR (“Bhuvan”, “bhu”);
O/p: INSTR(Bhuvan, van)
4
Example2: SELECT INSTR(“Bhuvan”, “kri”);
O/p : INSTR(Bhuvan,kri)
0
5. length ( ) : It returns number of character in the
string(inc. space)
No. of arguments: 1
Argument: String Return: Integer
Syntax: SELECT LENGTH(str);
Example: SELECT LENGTH(“Balaji sir”);
O/p: LENGTH(Balaji sir)
10
6. left ( ) : It returns N numbers of characters from
beginning of string
No. of arguments: 2
Argument: String, Integer Return: String
Syntax: SELECT LEFT(str, N);
Example: SELECT LEFT(“Balaji sir”, 4);
O/p: LEFT(Balaji sir,4)
Bala
7. right ( ) : It returns N numbers of characters
from ending of string
No. of arguments: 2
Argument: String, Integer Return: String
Syntax: SELECT RIGHT(str, N);
Example: SELECT RIGHT(“Balaji sir”, 3);
O/p: RIGHT(Balaji sir,3)
sir
8. lower ( ) / Lcase( ) : converts the string to
lowercase
No. of arguments: 1
Argument: String Return: String
Syntax: SELECT LOWER(str);
Example: SELECT LOWER(“CLASS 12C5”);
O/p: LOWER(CLASS 12C5)
class 12c5
Example2: SELECT LOWER(“classroom”);
O/p: LOWER(classroom)
classroom
9. upper ( ) / ucase( ) : converts the string to
UPPERCASE
No. of arguments: 1
Argument: String Return: String
Syntax: SELECT UPPER(str);
Example: SELECT UPPER(“staffroom”);
O/p: UPPER(staffroom)
STAFFROOM
10. concat: used to combine one or more string
with another string
No. of arguments: Infinite
Argument: String1, String2,…. Return: String
Syntax: SELECT CONCAT(str1,str2,…);
Example: SELECT CONCAT(“Bhuvan”,
“Krishna”);
O/p: CONCAT(Bhuvan, Krishna)
BhuvanKishna
Example2: SELECT CONCAT(“NJ12”, “ ”,
“C5”);
O/p: CONCAT(NJ12, ,C5)
NJ12 C5
11.substr( ) / mid( ) / substring( ):
It returns N number of character from middle of
the string
No. of arguments: 3
Argument: String, Integer, Integer
Return: String
Syntax: SELECT MID(str,n1,n2);
n1 – starting position
n2 – number of characters
Example: SELECT MID(“informatics
practices”,3,6);
O/p: MID(informatics practices,3,6)
format
_________________________________________
Numeric
1. pow( ) / power( ) : It returns X raised to power
of Y. (xy )
No. of arguments: 2
Argument: Integer Return: Integer
Syntax: SELECT POW(x,y);
Example: SELECT POW(2,4);
O/p: POW(2,4)
8
2. mod( ) : It returns the reminder of X divided
by Y. ( X )
Y
No. of arguments: 2
Argument: Integer Return: Integer
Syntax: SELECT MOD(x,y);
Example: SELECT MOD(12,5);
O/p: MOD(12,5)
2
Example2: SELECT MOD(4,2);
O/p: MOD(4,2)
0
3. round( ) : It rounds number to n decimal places.
• n can be both positive and negative
• if n is not given by default n = 0
No. of arguments: 1/2
Argument: Integer Return: Integer
Syntax: SELECT ROUND(int,n);
Example1: SELECT ROUND(4261.533,4);
O/p: ROUND(4261.53375,3)
4261.534
Example2: SELECT ROUND(4261.53375,-2);
O/p: ROUND(4261.53375,-2)
4300
Date And Time
(YYYY--MM--DD:HH:MM:SS)
1. now ( ) : It returns current date and time
No. of arguments: None
Argument: None Return: Date and time
Syntax: SELECT NOW ( );
Example: SELECT NOW ( );
O/p: NOW ( )
2022--07--13 08:05:10
2. sysdate( ) : It returns the date and time at the
time of result
No. of arguments: None
Argument: None Return: Date and time
Syntax: SELECT SYSDTATE ( );
Example: SELECT SYSDATE ( );
O/p: SYSDATE ( )
2022--07--13 08:05:08
3. day: It returns only date from the argument
No. of arguments: 1
Argument: Date and Time
Return: Date and time
Syntax: SELECT DAY (date&time);
Example: SELECT DAY (date&time);
O/p: DAY (“2022--03--28 09:30:10”)
28
4. month: It returns only month from the argument
No. of arguments: 1
Argument: Date and Time
Return: Date and time
Syntax: SELECT MONTH (date&time);
Example: SELECT MONTH (date&time);
O/p: DAY (“2022--03--28 09:30:10”)
03
5. year: It returns only year from the argument
No. of arguments: 1
Argument: Date and Time
Return: Date and time
Syntax: SELECT YEAR (date&time);
Example: SELECT YEAR (date&time);
O/p: YEAR (“2022--03--28 09:30:10”)
2022
6. dayname( ): It returns day name from the
argument
No. of arguments: 1
Argument: Date and Time
Return: Date and time
Syntax: SELECT DAYNAME (date&time);
Example: SELECT DAYNAME (date&time);
O/p: DAYNAME (“2022--03--28 09:30:10”)
monday
7. monthname( ): It returns month name from the
argument
No. of arguments: 1
Argument: Date and Time
Return: Date and time
Syntax: SELECT MONTHNAME (date&time);
Example: SELECT MONTHNAME (date&time);
O/p: MONTHNAME (“2022--03--28 09:30:10”)
march
8. date( ): It returns date part from the argument
No. of arguments: 1
Argument: Date and Time
Return: Date and time
Syntax: SELECT DATE (date&time);
Example: SELECT DATE (date&time);
O/p: DATE (“2022--03--28 09:30:10”)
2022--03--28
________________________________
