# MySQL Advanced Functions

## MySQL BIN() Function

The `BIN()` method returns a binary representation of Numeric value, where Numeric value is a longlong (BIGINT) number.

This is equivalent to `CONV(N,10,2)`. Returns NULL if Number value is NULL.

### Syntax:

Here is the syntax of MySQL BIN() function:

``BIN(numeric value)``

Parameter Values

Parameter Description
numeric value Required. A numeric value.

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example 1: This example shows how to represent the number 2 in binary:

``SELECT BIN(2);``

Result:

10

As shown in the output, the function will return the binary value 2 as 10.

Example 2: This example shows how to represent the number 8 and 18 in binary

``SELECT BIN(8), BIN(18);``

Result:

BIN(8) BIN(18)
1000 10010

As per the result, a binary value of 8 is 1000, and the binary value of 18 is 10010.

Example 3: This example use decimal number as a number value:

``SELECT BIN(1.3), BIN(3.2);``

Output:

BIN(1.3) BIN(3.2)
1 11

The result is used to convert decimal numbers 1.3 and 3.2 to their binary equivalent 1 and 11.

Example 4: When this function use letters as a numeric value what will happen let's see in this example:

``SELECT BIN('J'),BIN('3J');``

Result:

BIN('J') BIN('3J')
0 11

Now you can see that the result of the Single letter j is 0, but when you give numeric and string together("3J") this function will return only the numeric value's(3) equivalent binary value(11).

Example 5: If we give a null value in this function what will happen let's see in this example:

``SELECT BIN(NULL);``

Result:

NULL

Whenever the function numeric value is NULL the output is also NULL.

## MySQL BINARY Function

The `BINARY` function converts a value into a binary string.

This function is equivalent to using `CAST (value AS BINARY)`.

The BINARY function accepts one parameter.

### Syntax

Here is the syntax of BINARY function:

``BINARY value``

Parameter Values

Parameter Description
value Required. The value to change.

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example 1: Use the formula below to convert a value to a binary string:

``SELECT BINARY "simmanchith.com";``

Result:

simmanchith.com

The value was converted into binary string in this result.

Example 2: Comparison of two strings character by character without using the BINARY function:

``SELECT "SIMMANCHITH" = "simmanchith";``

Result:

1

The result is to compare value character by character result is true so the output is 1.

Example 3: This example uses the BINARY function Byte-by-Byte comparison of two string:

``SELECT BINARY 'SIMMANCHITH' = 'simmanchith';``

Result:

0

MySQL compares "SIMMANCHITH" and "simmanchith" comparison of two strings is 0 because it compares byte-by-byte (Capital letter and small letter holds different byte values).

To better unserstanding check the below example also:

``SELECT BINARY 'SIMMANCHITH' = 'SIMMANCHITH';``

Result:

1

Here the comparison of two strings is 1 because the comparison bytes hold the same value.

## MySQL CASE Function

Main Article :- MySql CASE() Statement

When any condition passes the given statement, the `CASE()` method in MySQL is used to determine a value by passing over conditions; otherwise, it returns the statement in an else section. When a condition is met, although, it stops reading and produces the output.

Advance Features:

If none of the above conditions are true, this method returns the statement in the else part.

If none of the conditions are true, and there is no other portion, this method returns NULL.

### Syntax:

Here is the syntax of CASE function:

``````CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;``````

Parameter Values

Parameter Description
condition1, condition2, ...conditionN Required. Because of the conditions. These are graded in the order specified on the list.
result1, result2, ...resultN Required. This is the value that should be returned if a condition is true.

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example 1: When the first of the conditions is met, return a value, as seen in this example:

Take the EMPLOYEESIMPLE table and perform the following query:

EmployeeName Dept Salary
Hari Krishnan Programming 25000
Dharan Web Design 35500
Vinoth Kumar Administration 21500

The query will be like this,

``````SELECT EmployeeName, Dept, Salary,
CASE
WHEN Salary > 25000 THEN "The salary is greater than 25000"
WHEN Salary = 25000 THEN "The salary is 25000"
ELSE "The salary is under 25000"
END
FROM EMPLOYEESIMPLE;``````

Result:

EmployeeName dept salary Salary Information
Hari Krishnan Programming 25000 The salary is 25000
Dharan Web Design 35500 The salary is greater than 25000
Vinoth Kumar Administration 21500 the salary is under 25000

From the result, It checks the first condition, if the salary is greater than 25000 the salary information "The salary is greater than 25000" will be the output, Then it checks the second condition. if the salary is equal to 25000 when the condition satisfies the salary information "The salary is 25000" will be the output. After all the condition fails the output will be "The salary is under 25000".

Example 2: The SQL below will sort the CountryName by Code. If Code is NULL, however, arrange by CountryName:

Take the COUNTRY table and perform the following query:

Code CountryName Continent
CUB Cuba North America
GTM Guatemala North America
ITA Italy Europe

The query will be like this,

``````SELECT CountryName, Code, Continent
FROM Country
ORDER BY
(CASE
WHEN Code IS NULL THEN Continent
ELSE Code
END);``````

Result:

CountryName Code Continent
Cuba CUB North America
Guatemala GTM North America
Italy ITA Europe

This result works based on an order by certain conditions, When the code column is null then the continent will be returned, otherwise, the code will return again from the country table.

## MySQL CAST() Function

Main Article :- MySql CAST() Function

In MySQL, the `CAST()` function converts a value from one data type to another data type (of any type) provided in the expression.

It's primarily used in conjunction with WHERE, HAVING, and JOIN clauses.

This function works similarly to MySQL's `CONVERT()` function.

### Syntax

Here is the syntax of CAST function:

``CAST(expr AS type [ARRAY])``

Parameter Values

Parameter Description
expr Required. convert specific expression.
datatype Required. datatype to convert.
Value Description
DATE Value to DATE conversion. "YYYY-MM-DD" is the format.
DATETIME DATETIME is converted from value. "YYYY-MM-DD HH:MM:SS" is the format for the date.
DECIMAL DECIMAL value is converted. Define the maximum number of digits (M) and the number of digits after the decimal point (D) using the optional M and D parameters (D).
TIME TIME is converted from value. "HH:MM:SS" is the preferred format.
CHAR Value is converted to CHAR (a fixed length string).
NCHAR NCHAR value conversion (like CHAR, but produces a string with the national character set)
SIGNED Value to SIGNED conversion (a signed 64-bit integer).
UNSIGNED UNSIGNED is converted to value (an unsigned 64-bit integer).
BINARY Converts value to BINARY (a binary string)

Technical Details

Works in: From MySQL 4.0

### Example3:-

Example 1: To convert date/time information represented by strings to DATE, DATETIME type data, use the CAST() method:

``````SELECT
CAST('2021-04-18' AS DATE) AS DateCasting,
CAST('12:12:12' AS TIME) AS TimeCasting,
CAST('2021-04-18 10:10:10' AS DATETIME) AS DateTimeCasting;``````

Result:

DateCasting TimeCasting DatetimeCasting
4/18/2021 12:00:00 AM 12:12:12 4/18/2021 10:10:10 AM

Date, Time, and DateTime are all converted to another datatype as a result of this.

Example 2: Use the example below to convert a value to the CHAR datatype.:

``SELECT CAST(100 AS CHAR);``

Result:

100

The value 100 is transformed to the char datatype in this result.

Example 3: To convert a value to a SIGNED datatype, use the following example:

``SELECT CAST(9-19 AS SIGNED);``

Result:

100

Through the result the range of number 9-19 is converted as SIGNED datatype.

Example 4: You can also get the binary string of a standard string using the BINARY operator, as seen below:

``SELECT CAST('welcome' AS BINARY);``

Result:

0x68656C6C6F

From the result, you can see that in MySQL, the binary string "welcome" is printed into hexadecimal.

## MySQL COALESCE() Function

Main Article :- MySql COALESCE() Function

If there are no non-NULL values in a list, the `COALESCE()` function returns NULL.

The aggregated type of the argument types is returned by COALESCE().

### Syntax

Here is the syntax of the MySQL COALESCE() function:

``COALESCE(value1, value2, ...., value_n)``

Parameter Values

Parameter Description
value1, value2, value_n Required. The testing value.

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example 1: In this example from a list, get the first non-null value:

``SELECT COALESCE(NULL, NULL, NULL, 'simmanchith.com', NULL, 'Example.com');``

Result:

simmanchith.com

From the example, you can see that there are two null values and four null values. The coalesce function returns the first no-null value, 'simmanchith.com'.

Example 2: The first non-null value in the example In a list:

``SELECT COALESCE(NULL, 5, 9, 'simmanchith.com');``

Result:

5

From the result, first no null value is 5 it will return.

## MySQL CONNECTION_ID() Function

The `CONNECTION ID()` method returns the distinctive connection ID for the current connection.

### Syntax

Here is the syntax of the MySQL CONNECTION_ID() function:

``CONNECTION_ID()``

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example: The unique connection ID of the current connection is returned:

``SELECT CONNECTION_ID();``

Result:

69

This result will show your systems current connection.

## MySQL CONV() Function

The `CONV()` method converts an integer to a string value from one numeric base system to another. From base 10 to base 2, for example.

The argument N is treated as an integer, although it can also be supplied as a string.

The smallest base is 2 and the largest is 36. N is considered a signed number if from base is a negative number.

N is unsigned otherwise. The precision of CONV() is 64 bits.

Note:If any of the parameters are NULL, this method returns NULL.

### Syntax

Here is the syntax of the MySQL CONV() function:

``CONV(number, from_base, to_base)``

Parameter Values

Parameter Description
number Required. A number.
from_base The number system's numeric base (a number between 2 and 36)
to_base To convert to, choose a numeric base system (a number between 2 and 36 or -2 and -36)

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example 1: The following formula can be used to convert a number from numeric base system 10 to numeric base system 2:

``SELECT CONV(20, 10, 2);``

Result:

10100

As a result, the number 20 converts base 10 to base 2 and returns a binary value.

Example 2: The following formula can be used to convert a number from numeric base system 2 to numeric base system 10:

``SELECT CONV(10100, 2, 10);``

Result:

20

As per result, the binary number 10100 converts base 2 to base 10 and returns a value.

Example 3: Use the CONV function to convert a number from the numeric base system 10 to the numeric base system 16:

``SELECT CONV(75, 10, 16);``

Result:

4B

From the result, base 10 decimal convert to base 16 hexadecimal the output will value 4B.

## MySQL CONVERT() Function

The `CONVERT()` function in MySQL transforms any type of parameter value to the given type or character set.

Note: The CAST() method nearly matching to this function.

### Syntax

Here is the syntax of the MySQL CONVERT() function:

``CONVERT(expr, type)``

or

``CONVERT(expr USING charset)``

Parameter Values

Parameter Description
expr Required. value to convert.
type Required. The datatype to convert. Can be one of the following:
Value Description
DATE Value to DATE conversion. "YYYY-MM-DD" is the format.
DATETIME DATETIME is converted from value. "YYYY-MM-DD HH:MM:SS" is the format for the date.
DECIMAL DECIMAL value is converted. Define the maximum number of digits (M) and the number of digits after the decimal point (D) using the optional M and D parameters (D).
TIME TIME is converted from value. "HH:MM:SS" is the preferred format.
CHAR Value is converted to CHAR (a fixed length string).
NCHAR NCHAR value conversion (like CHAR, but produces a string with the national character set)
SIGNED Value to SIGNED conversion (a signed 64-bit integer).
UNSIGNED UNSIGNED is converted to value (an unsigned 64-bit integer).
BINARY Converts value to BINARY (a binary string)
charset Required. The character set to convert to

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example 1: To convert date/time information represented by strings to DATE, DATETIME type data, use the CONVERT() method:

``````SELECT
CONVERT('2022-08-04' AS DATE) AS DateConversion,
CONVERT('02:12:08' AS TIME) AS TimeConversion,
CONVERT('2022-08-04 08:08:19' AS DATETIME) AS DateTimeConversion;``````

Result:

DateConversion TimeConversion DateTimeConversion
8/4/2022 12:00:00 AM 02:12:08 8/4/2022 8:08:19 AM

Date, Time, and DateTime are all converted to another datatype as a result of this.

Example 2: To convert a value to the CHAR datatype, use the example below:

``SELECT CONVERT(100, CHAR);``

Result:

100

The value 100 is transformed to the char datatype in this result.

Example 3: Use the example below to convert a value to the LATIN1 character set:

``SELECT CONVERT("simmanchith.com" USING latin1);``

Result:

simmanchith.com

The result will convert input string "simmanchith.com" into LATIN1 character set.

## MySQL CURRENT_USER() Function

The `CURRENT_USER()` method returns the user name and host name of the MySQL account that was used by the server to authenticate the current client.

Your access permissions are determined by this account. A string in the utf8 character set is returned.

Note: The value of CURRENT_USER() can differ from the value of `USER()`.

### Syntax:

Here is the syntax of the MySQL CURRENT_USER() function:

``CURRENT_USER()``

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example: This example, return the user name and host name from MySQL account:

``SELECT CURRENT_USER();``

Result:

root@localhost

From the result, you can findout your current user name "root" and host name "localhost".

## MySQL VERSION() Function

The `VERSION()` function returns a string representing the MySQL database's current version. The >utf8 character set is used in the string. In addition to the version number, the value could have a suffix.

For statement-based duplication, this function is toxic. If `binlog_format` is set to STATEMENT and this function is used, a warning is recorded.

### Syntax:-

Here is the syntax of the MySQL VERSION() function:

``VERSION()``

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example: This example for return the MySQL database's current version:

``SELECT VERSION();``

Result:

8.0.28

The result is displayed based on your current version of the MySQL account in your system.

## MySQL USER() Function

As a string in the utf8 character set, the `USER()` method delivers the current MySQL user name and host name.

This function is equivalent to both the `SESSION_USER()` and `SYSTEM_USER()` functions.

Note: This function same CURRENT_USER() method.

### Syntax

Here is the syntax of the MySQL USER() function:

``USER()``

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example : The user name and host name of the current MySQL connection are returned in this example :

``SELECT USER();``

Result:

root@localhost

The root is the current username of MySQL, and localhost is the current hostname.

## MySQL SYSTEM_USER() Function

The function `SYSTEM_USER()` is not the same as the permission SYSTEM USER. The former gives you the user name and host name of your existing MySQL connection.

Note: This function is the same as the SESSION USER() and USER() functions.

### Syntax

Here is the syntax of the MySQL USER() function:

``SYSTEM_USER()``

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example :The user name and host name of the current MySQL connection are returned in this example:

``SELECT SYSTEM_USER();``

Result:

root@localhost

The outcome is depending on the current version of your MySQL account in your system.

## MySQL SESSION_USER() Function

SESSION_USER() returns the user name and host name for the current MySQL connection.

Note: This function is the same as SYSTEM USER() and USER().

### Syntax

Here is the syntax of the MySQL SESSION_USER() function:

``SESSION_USER()``

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example: This example displays the user name and host name of the current MySQL connection:

``SELECT SESSION_USER();``

Result:

root@localhost

The result will display the current localhost and username, respectively, localhost and root.

## MySQL NULLIF() Function

If two expressions are equivalent, the `NULLIF()` method returns NULL. otherwise, if this is not the case, the first expression is returned.

This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

### Syntax

Here is the syntax of the MySQL NULLIF() function:

``NULLIF(expression1, expression2)``

Parameter Values

Parameter Description
expression1, expression2 Required. Two comparison expressions.

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example 1: This example to compare two expressions:

``SELECT NULLIF(45, "India");``

Result:

45

From the result, the Nullif function checks for equavalent expressions, but here both are different, so as per function rule, the first expression 45 is shown as a result.

Example 2: In this example lets Compare two string expressions:

``SELECT NULLIF("Simman", "chith");``

Result:

simman

The result is simman, because here given both strings are not equal.

Example 3: Let's compare two equal expressions:

``````SELECT
NULLIF("2022-05-12", "2022-05-12") AS DateExpression,
NULLIF(25, 25) AS NumericExpression,
NULLIF("Welcome", "Welcome") AS StringExpression;``````

Result:

DateExpression NumericExpression StringExpression
NULL NULL NULL

From the result, two equal expressions are compared as per functionality. If there are two expressions that are equal, the output will be NULL, so the result is NULL.

## MySQL LAST_INSERT_ID() Function

The `LAST_INSERT_ID()` function returns the AUTO INCREMENT id of the last row entered or updated in a table.

LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) integer with no argument, which is the first automatically produced value successfully inserted for an AUTO INCREMENT column as a result of the most recently performed INSERT statement. If no rows are successfully inserted, the value of LAST_INSERT_ID() remains constant.

With an argument, LAST_INSERT_ID() returns an unsigned integer.

### Syntax:

Here is the syntax of the MySQL LAST_INSERT_ID() function:

``LAST_INSERT_ID(expr)``

Parameter Values

Parameter Description
expr Optional. An expression.

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example 1: The table's most recently inserted or updated row's AUTO INCREMENT id is returned in this example:

``SELECT LAST_INSERT_ID();``

Result:

0

There is no recent row inserted in the table so the outout is 0.

Example 2: This example to select last_inserted_id:

Step 1: Create Table

``````CREATE TABLE LastInsert
(
user_id int NOT NULL AUTO_INCREMENT,
product VARCHAR(10),
rs int,
sales int,
PRIMARY KEY(user_id)
);``````

Step 2: Insert data

``````INSERT LastInsert(product, rs, sales)
VALUES ('book1', 250, 240);

INSERT LastInsert(product, rs, sales)
VALUES ('book2', 350, 320);

INSERT LastInsert(product, rs, sales)
VALUES ('book3', 400, 350);``````

Step 3: The query,

``SELECT LAST_INSERT_ID() FROM LastInsert;``

Result:

3
3
3

From the above table 3 data's inserted lastly.

## MySQL ISNULL() Function

Main Article :- MySql ISNULL() Function

To check if a value is NULL, `ISNULL()` can be used instead of `=`.

ISNULL() has some similar characteristics to the IS NULL comparison operator.

This method returns 1 or 0 depending on whether an expression is NULL.

If the expression is NULL, this procedure returns 1. Otherwise, a value of 0 is returned.

Note: Comparing a value to NULL using = always yields NULL.

### Syntax

Here is the syntax of the MySQL ISNULL() function:

``ISNULL(expr)``

Parameter Values

Parameter Description
expr Required. testing value.

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example 1: This example checking for empty string is null or not:

``SELECT ISNULL("");``

Result:

0

This function output is 0, because the expression is not null here.

Example 2: Let's check bunch of expressions in this example:

``````SELECT
ISNULL(NULL) AS "Check NULL",
ISNULL("J") AS "Check Letter",
ISNULL(0) AS "Check Zero",
ISNULL(120) AS "Check Number";``````

Result:

Check Null Check Letter Check Zero Check Number
1 0 0 0

From the result, first we input NULL inside isnull function the result is true. so the output is 1, and the other inputs hold some values, so the result can't be null. Because of that, the result is 0.

## MySQL IFNULL() Function

The `IFNULL()` method returns a specified value if the expression is NULL.

This method returns the expression if the expression is not NULL.

### Syntax

Here is the syntax of the MySQL IFNULL() function:

``IFNULL(expr, substitute_value)``

Parameter Values

Parameter Description
expr Required. The expression to test whether is NULL.
substitute_value Required. If expression is NULL, this value is returned.

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example 1: The value supplied will be returned. Return the expression if it is NULL; else, return:

``SELECT IFNULL("Welcome", "Simmanchith.com");``

Result:

Welcome

The result is welcome, the input expression is not null.

Example 2: The value provided will be returned. Return NULL if the expression is NULL; else, return:

``````SELECT
IFNULL(NULL, 500) AS NumericNull,
IFNULL(NULL, "Simmanchith.com") AS StringNull;``````

Result:

NumericNull StringNull
500 Simmanchith.com

From the result, you can see that input values is null, so the substitute string will be returned as an output.

## MySQL IF() Function

The MySQL `IF()` function returns a value depending on a condition and is one of the MySQL control flow functions.

The IF() method returns one value if a condition is TRUE, and another value if it is FALSE.

The IF function is sometimes referred to as `IF ELSE` or `IF THEN ELSE` function.

### Syntax

Here is the syntax of the MySQL IF() function:

``IF(condition, value_if_true, value_if_false)``

Parameter Values

Parameter Description
condition Required. The condition to the value.
value_if_true Required. Return the condition if value True.
value_if_false Required. Return the value condition if value FALSE

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example 1: This example return 5 if the condition is TRUE; 10 if it is FALSE.:

``SELECT IF(250<1000, 10, 15);``

Result:

10

The condition will be tested based on the result, and because the condition is true, the output will be 10.

Example 2: If two strings are the same in this example, return "YES"; if they aren't, return "NO":

``SELECT IF(STRCMP("welcome","simmanchith") = 0, "YES", "NO");``

Result:

NO

The result is NO, because the two input strings compared in strcmp function both strings "welcome", and "simmanchith" are not equal.

Example 3: This example check if the condition is TRUE, return "YES," else, return "NO":

``SELECT IF(50<100, "YES", "NO");``

Result:

YES

The condition (50<100) will be tested based on the result, and because the condition is false, the else part will work so the output will be No.

Example 4: Return "MORE" if the condition is TRUE, and "LESS" if it is FALSE, as shown in the example:

Let's take "STUDENT" table to execute the query:

StudentName Sub1
Bala Murugan 55
Rishi Keshan 44
``````SELECT
StudentName, Sub1,
IF(Sub1>50, "MORE", "LESS") AS ConditionResult
FROM STUDENT;``````

Result:

StudentNname Sub1 ConditionResult
Bala Murugan 55 MORE
Rishi Keshan 44 LESS

We choose two columns from the result, StudentName and Sub1, and check the condition. Bala Murugan Sub1 > 50 Because the criterion is met, the result is "MORE." Rishi Keshan Sub1 > 50, on the other hand, the condition is false, hence the result is "LESS."

## MySQL DATABASE() Function

The default (current) database name as a string in the utf8 character set is returned by the `DATABASE()` method.

DATABASE() returns NULL or "" when there is no default or current database. The default database in a stored routine is the database with which the routine is related, which may or may not be the same as the caller context's default database.

### Syntax

Here is the syntax of the MySQL DATABASE() function:

``DATABASE()``

Technical Details

Works in: From MySQL 4.0

### Examples:-

Example: This example to return current (default) database's name:

``SELECT DATABASE();``

Result:

simmanchithdb

You can see the current or default database name is simmanchithdb from the result.