What are Functions?
Functions are predefined formulae, also known as calculation
engines, because functions deliver their results quickly by
internally calculating multiple mathematical expressions.
In simple words, we can say function is a built-in formula to
accomplish a particular task. A function may require some
data to act upon. A function may return some result.
Using computer languages such as C, C++, JAVA etc. we can
create our own functions (known as user defined functions),
which can be used as and when required in the software.
Most of the software consists of a set of functions, only.
Functions in MySQL
SQL functions
Single Row Functions Multiple Row Functions
Single & Multiple Row functions
Single Row Functions: Such functions operate on the single row and
return one result per row. They accept one or more arguments and
return one value for each row. They can e used with SELECT, WHERE
and ORDER BY clauses, and can be nested.
For example,
Select ucase(name) from scholar where sub = ‘science’;
Multiple Row Functions: such functions operate on the collection of the
rows, and return an answer. Aggregate functions are Multiple Row
functions, as they act on number of rows. For example,
Select avg(fee) from fees;
Character Functions
Character or String Functions: This group of functions is used
with character variables. Such functions take character
data as input and returns either character or number values.
Various Character functions, are:
1. Concat(fieldnames or expressions to be combined): It is
used to combine the given list of values.
Select concat(rollno, name, subject) from student;
1101ROHANARTS
1102MITASCIENCE
Select concat(“hello”, “India”);
helloIndia
Character Functions
2. Left(fieldname or string, Number of characters): It is used to
retrieve number of characters from the left-hand side of the given
field or string value. For example,
Select left(name, 3) from student where sub = ‘arts’;
Roh
Mit
Select left("Ajmer Jaipur", 7);
Ajmer J
3. Right(fieldname or String): It is used to retrieve characters from
right-hand side of the String or field. For example,
Select Right(name, 3) from student where sub = ‘arts’;
han
ita
Character Functions
4. Substr(fieldname or String, offset, number of character): It
is used to retrieve number of characters from the given
offset position in the String. For example,
select name, substr(name, 2, 3) from student;
Scott cot
Priyanka riy
5. Mid(): it is similar to substr() function.
6. Lcase() and Ucase(): Lcase() returns the lower case of the
string, whereas Ucase() returns upper case. For example,
Select ucase(name), lcase(name) from student;
SCOTT scott
PRIYANKA priyanka
Character Functions
7. Length(fieldname or string): It returns total number of
characters present in a string, including leading & trailing
spaces, and spaces in between the words.
Select length(“Anselms Ajmer”);
13
8. Instr(fieldname or string, substring): It returns the position
of the substring in the given field name or string. It returns
0 if substring is not present in the fieldname or string.
Select instr("Anselms", "se");
3
Select instr("Anselms", “te");
0
Character Functions
9. Trim(fieldname or String): It is used to remove leading and
trailing spaces from the string. It is mostly used with
length() function. For example,
select length(" Ajmer Jaipur "); 17
select length(trim(" Ajmer Jaipur ")); 12
10. LTRIM() & RTRIM(): Ltrim() is used to remove leading
spaces from the string, whereas Rtrim() removes trailing
spaces. From example,
select length(ltrim(" Ajmer Jaipur “)); 14
select length(Rtrim(" Ajmer Jaipur “)); 15
Character Functions
11. repeat(Fieldname or String, Number of times): It is used
to repeat a string for number of times, for example:
select repeat("hello", 3);
Hellohellohello
12. replace(fieldname or String, character(s) to be replaced,
replace with): This function is used to replace a
character(s) with the given character(s). For example,
select replace("hello", 'e', ‘a');
hallo
select replace("heello", "ee", 'w');
hwllo
select replace("heello", 'e', 'TT');
hTTTTllo