In MySQL Advanced Function Part-4 we will discuss about the IFNULL, IN, INET6_ATON, INET6_NTOA, INET_ATON, INET_NTOA, ISNULL, LAST_VALUE, LEAD
IFNULL Function:- IFNULL Function is used to check given if the expression is NULL.
Syntax:- IFNULL(check expression, return value expression)
Example:-
SELECT IFNULL(NULL, “kshtutor.com”);
Output:- kshtutor.com
IN Function:- IN Function is used for finding a match from a given argument. It returns 1 if IN() list matches. else return 0.
Syntax:- IN(v1, v2, v3,….)
Example:-
SELECT 12 IN(6,12,18);
output:- 1
INET_ATON Function:- INET_ATON Function is used for returning the numeric value of a given IPv4 value.
Syntax:-(expression)
Example:-
SELECT INET_ATON(‘27.1.0.1’);
Output:- 453050369
Note:- the formula is if IPv4 value is a. b. c. d then a×2563+ b×2562+ c×2561 + d
INET_NTOA Function:- INET_NTOA Function is the reverse of the INET_ATON Function. It takes the IPv4 address in byte order and returns a dotted-quad string representation.
Syntax:- INET_NTOA(expression)
Example:-
SELECT INET_NTOA(‘453050369’);
Output:- 27.1.0.1
INET6_ATON Function:- INET6_ATON Function is used for returning binary string value of a given IPv4 value IPv6 dotted address.
Syntax:- INET6_ATON(expression)
Example:-
SELECT INET6_ATON(‘27.1.0.1’);
INET6_NTOA Function:- INET6_NTOA Function is used for returning the numeric value of a given IPv4 orIPv6 binary format string address.
syntax:- INET6_NTOA (IPv4 or IPv6 address)
ISNULL Function:- ISNULL Function is used to check whether the expression is NULL or NOT NULL. If the expression is NULL it returns 1 otherwise it returns 0.
Syntax:- ISNULL(expression)
Example:-
SELECT ISNULL(NULL);
Output:- 1
LAST_VALUE Function:- LAST_VALUE Function is used to get the last value in a set of values. It is used with the OVER clause.
Syntax:- Last_Value (expression) OVER (
[partition_clause]
[order_clause]
[frame_clause]
)
LEAD Function:- LEAD Function is used for getting succeed row from the current row. It is used with the OVER clause. It is a window function.
Syntax:- LEAD(expr, N, default) OVER (Window_specification | Window_name)
N is a positive value.
Example:-
SELECT stuId, exm_start, exam_end – LEAD(exm_start) OVER ( ORDER BY exm_start) + 1 as ‘total_days’ FROM examination.
Read Also:-
MySQL Advanced Function Part-1
MySQL Advanced Function Part-2
MySQL Advanced Function Part-3
MySQL Advanced Function Part-5
Read more detail about functions in the MySQL manual.
