MYSQL FUNCTIONS
A Function is a predefined set of statements (program) to
accomplish a particular specified task. The functions work
on passed arguments and then return a value.
In MySQL, there are many categories of functions
depending on what type of arguments they work on:
Single Row Functions – They work with a single reord at a
time and return a result for every record they work on.
[ i.e if it works on 5 rows, 5 rows of result will appear. ]
Group functions – They work with data of multiple rows
and they return a single result value for that group of
rows. [i.e. if it works on 5 rows, 1 row of result will appear.
MYSQL FUNCTIONS
Categories of functions – depending on datatype of
parameters in work on:
• Numeric Functions
• String Functions
• Date & Time Functions
• Miscellaneous Functions
Numeric Functions – Single Row Function
Function name Syntax Example Output
Power() / Pow() - it returns n1 Power (n1, n2) Select power(3,4); 91
raised to power n2 Select pow(3,4); 91
Round() – it returns the fractional Round(n1 ,n2) Select
number rounded upto specified no. round(4567.672, 1); 4567.7
of places. [OR a whole number if n2 Note: n2 is Select
is not specified] optional. round(4567.672); 4568
Mod() – it reurns remainder on n1 / Mod(n1, n2) Select mod(49, 6); 1
n2
Truncate() - it returns the fractional Truncate(n1,n2) Select truncate
number upto Zero or specified no. (4567.672, 1); 4567.6
of places w/o rounding
Select truncate
(4567.672); 4567
Sqrt() - it returns square root of Sqrt(n) Select sqrt(10); 3.1622
given number. Select sqrt(29.3); 5.4129
String Functions – Single Row Function
Lower() / Lcase()- it returns string in lower case
Syntax: Lcase(string / FieldName)
Lower(string / FieldName)
Eg:
Select lower(“ABC”);
Select lower(colname) from students;
String Functions – Single Row Function
Upper() / Ucase()- it returns string in CAPITAL case
Syntax: ucase(string / FieldName)
upper(string / FieldName)
Eg: Select ucase(string / Fieldname);
Select upper(Fname) from students;
String Functions – Single Row Function
Length(str/fieldname) – returns no. of characters in specified
parameter include spaces and any special character.
Syntax: Length(String / Fieldname)
Eg: Select length(“ Functions in MySQL “);
Select name, length(name) from
students;
String Functions – Single Row Function
left(str/FieldName,n) – returns n characters from extreme left
of str / Fieldname Value
Syntax: left(string / FieldName , n)
Eg:
Select left(“ Function”,4) ;
Select left(name,2) from students;
String Functions – Single Row Function
right(str/FieldName,n) – returns n characters from
extreme right of str / Fieldname Value
Syntax: right(string / FieldName , n)
Eg: Select right(“ Function”,4) ;
Select right(name,2) from
students;
String Functions – Single Row Function
Substring/substr/mid(str,n1,n2)- returns n2 no. of characters from str starting from
n1 index no. n2 is optional, if not given it returns a string starting from character at
n1 index no to end of string. If n1 is a negative number, it starts from back of the
string.
Note: [in SQL indexing starts from 1]
Syntax: select substring(“ String” / Fieldname,n1, n2);
Eg: select substring(“ABC-123”,2,4);
select mid(“ABC-123”,4);
select substr(name,4) from students;
String Functions – Single Row Function
Instr() – returns index no. of first occurrence of substr in given
str. [NOTE: index no starts from 1]
Syntax: Instr(str, substr)
Eg: Select INSTR(“Prevention is Better than Cure”, “e”) ;
Select instr(name,”et”) from students;
String Functions – Single Row Function
Ltrim(str) – removes spaces from extreme left
Rtrim(str) – removes spaces from extreme right
Trim(str) – removes spaces from extreme left & right both