Loading

MySQL Advanced Function Part-4

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.

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w
Verified by ExactMetrics