Loading

MySQL Advanced Function Part-2

In MySQL Advanced Function Part-2 we will discuss about the CAN_ACCESS_USER, CAN_ACCESS_VIEW, CASE, CAST, COALESCE, CONNECTION_ID, CONV, CONVERT, CURRENT_ROLE

CAN_ACCESS_USER Function:- CAN_ACCESS_USER Function is used only for internal server operation.

CAN_ACCESS_VIEW Function:- CAN_ACCESS_VIEW Function is used only for internal server operation.

CASE Function:- CASE Function is used for conditional construct. It works like a SWITCH statement that means IF THAN ELSE.

Syntax:- CASE

WHEN condition1 THEN result1

WHEN condition2 THEN result2

WHEN conditionN THEN resultN

ELSE result

END;

Example:-

SELECT rollnumber, name,

CASE

WHEN marks > 60 THEN “First”

WHEN marks < 30 THEN “Fail”

ELSE “Pass”

END

FROM students;

CAST Function:- CAST Function is used to convert any data type value into a specified datatype.

Syntax:- CAST( values AS datatype)

Example:-

SELECT CAST(“11:40” AS NCHAR);

Output:- 11:40

COALESCE Function:- COALESCE Function is used for returning the first null value in a given list.

Syntax:- COALESCE(v1, v2, v3,…..vn)

Example:-

SELECT COALESCE(5, 3, NULL, ‘kshtutor.com’);

Output:- 5

CONNECTION_ID Function:- CONNECTION_ID Function is used for MySQL’s unique connection id of MySQL’s current connection.

Syntax:- CONNECTION_ID()

Example:-

SELECT CONNECTION_ID();

CONV Function:- CONV Function is used for converting numbers from one numeric base system to another numeric base system. And it returns as a string.

Syntax:- CONV(number, from base, to base);

if either “from a base” or “to base” will be null then it will return NULL.

Example:-

Converting 10 base system to 16 base system.

SELECT CONV(79, 10, 16);

Output:- 4F

CONVERT Function:- CONVERT Function is used for converting the value into a specific data type or character set. This function is similar to CAST().

Syntax:- CONVERT(value, type);

Example:-

SELECT CONVERT(60, CHAR);

Output:- 60

CURRENT_ROLE Function:- CURRENT_ROLE Function displays the active role of the current MySQL database session.

Syntax:- CURRENT_ROLE();

Example:-

SELECT CURRENT_ROLE();

Output:- Just like (r1`@`%`)

Read Also:-

MySQL Advanced Function Part-1
MySQL Advanced Function Part-3
MySQL Advanced Function Part-4
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