«

»

May 16

Mysql Function

Mysql Function

Mysql inbuild function as follow by categories

Aggregate Function

AVG – Calculate Average value of given set of expression

Example:

select AVG(column) from tbl_name

Table (prdprice)

id Name Price
25 Pencil 250.00
35 Phone 1525.00
36 Pen 254.00
58 Note Book 450.00

select AVG(price) from prdprice

Output
AVG(price)
619.75

COUNT – Count the number of row in table.

Example

Select COUNT(*) from prdprice

Output
count(*)
4

Select Count(name) as ‘Total Rows’ from prdprice

Output
Total Rows
4

SUM – Calculate the total value of given set.

Example
SELECT SUM(price) FROM `prdprice`

Output
SUM(price)
2479

MIN – Show the minimum value of given set

Example
SELECT min(price) FROM `prdprice`

Output
min(price)
250

MAX – Show the maximum value of given set

Example
SELECT max(price) FROM `prdprice`

Output
max(price)
1525

Mysql String Function

CONCAT – Combine two or more string into single string.

Example
Select CONCAT(‘string1’, ‘string2’)

Output
string1 string2

LENGTH – show the length of string in byte

example
select length(‘string ‘

Output
length(‘string ‘)
7

CHAR_LENGTH – show the lenght of string in character

Example
select CHAR_LENGTH(‘string’)

Output
char_length(‘string’)
6

REPLACE – This function use to replace a substring in string

example
select replace(‘string1 string2’, ‘ ‘,’>’)

Output
replace(‘string1 string2′,’ ‘,’>’)
string1>string2

SUBSTRING – substring show a string starting from a position to given position

exmple
select substring(‘string1 string2’, 9,15)

Output

string2

LEFT – Get the left part of the string

example

select LEFT(‘mysql function’,5)

Output

mysql

FIND_IN_SET – find a string within a comma seperated list of string

example
SELECT FIND_IN_SET(‘q’,’x,y,z,q,c,d’);

Output

4

TRIM – Remove excess space on the left and right side of the string.

Example
SELECT TRIM(‘ MySQL Function ‘);

Output
MySQL Function

INSTR – Return a position of substring in string

Example
SELECT INSTR(‘MySQL Function’, ‘MySQL’);

Output
1

FORMAT – Format the number and round to the number of decimal.

Example
SELECT FORMAT(25600.2015, 2);

Output
25600.20

Control Flow Function

IF – Return the value base on given condition. If(expr1,expr2,expr3), if expr1 is TRUE then return value expr2 otherwise return value expr3. IF() return the numeric and string value, depending on the context in which it is used.

Example
select IF(1<2,’YES’,’NO’)
YES
SELECT IF(STRCMP(‘test’,’test1′),’no’,’yes’);
no

Return value Type
expr1 and expr2 is string then return value is string
expr1 and expr2 is integer the return value is integer

IFNULL – Return the first argument if it is not NULL, otherwise it return the second argument. Return the numeric and string value, depend on the context in wich it is used.

Example
Select IFNULL(1,0)
1
Select IFNULL(1/0,0)
0

NULLIF – The NULLIF function accept 2 argument. if first argument is equal to second argument then it return NULL otherwise it return the first argument.

Syntax
NULLIF(expr1, Expr2)

Example
select NULLIF(2,2)
NULL
select NULLIF(2,3)
2

CASE – if condition is true then return the corresponding THEN part result otherwise return ELSE part Result.

Syntax & Example

CASE
WHEN expr1='0' THEN TRUE
ESLE False
END

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>