Loading

MySQL Advanced Function Part-1

In MySQL Advanced Function Part-1 we will discuss about the ANY_VALUE, BENCHMARK, BIN, BINARY, BIN_TO_UUID, BIT_AND, BIT_COUNT, BIT_LENGTH, BIT_OR, BIT_XOR, CAN_ACCESS_COLUMN, CAN_ACCESS_DATABASE, CAN_ACCESS_TABLE

ANY_VALUE Function:- ANY_VALUE Function is used for returning any value in the specified column. ANY_VALUE Function is used with GROUP_BY statement. This function suppresses the error when ONLY_FULL_GROUP_BY mode is enabled.

Syntax:- ANY_VALUE(column name)

Example:-

SELECT ANY_VALUE(age) FROM student GROUP BY age-1;

BENCHMARK Function:- BENCHMARK Function is used for the measurement of query execution time.

Syntax:-(count, expression)

Example:-

SELECT BENCHMARK(500,PI( ));

BIN Function:- BIN Function is used for returning binary representation. That means it converts a decimal to binary. The BIN function is equivalent to the CONV function.

Syntax:- BIN(NUMBER)

Example:-

SELECT BIN(20);

Output:-10100

BIN_TO_UUID Function:- BIN_TO_UUID Function is used for converting UUID to string. It takes one or two arguments.

Syntax:-  BIN_TO_UUID(binary_uuid,swap_flag)

swap_flag is an optional argument.

SELECT BIN_TO_UUID(@binary_uuid, 1);

BINARY Function:- BINARY Function is used for converting any one value to a binary string.

Syntax:- BINARY(value)

Example:-

SELECT BINARY “kshtutor.com”;

Output:- kshtutor.com

SELECT BINARY “HAD” = “had”;

Output:- 0

BIT_AND Function:- BIT_AND Function is used for returning bitwise AND of all bits from a given expression. First of all, it converts all decimal values into binary values.

Syntax:- BIT_AND(expression)

Example:-

SELECT studentname, BIT_AND(annual_Fee) FROM dtudents group by  studentname;

BIT_COUNT Function:- BIT_COUNT Function is used for returning the number of bits set in a given argument.

Syntax:- BIT_COUNT(Number);

Example:-

SELECT BIT_COUNT(8);

Output:- 1

BIT_LENGTH Function:- BIT_LENGTH Function is used for finding the bit length of a given character string.

Syntax:- BIT_LENGTH(string)

Example:-

SELECT BIT_LENGTH(“kshtutor”);

Output:- 64

BIT_OR Function:- BIT_OR Function is used for returning bitwise or of all bits from a given expression.

Syntax:- BIT_OR(expression)

BIT_XOR Function:- BIT_XOR Function is used for returning bitwise XOR of all bits from a given expression.

Syntax:- BIT_XOR(expression)

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

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

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

Read Also:-

MySQL Advanced Function Part-2
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