MySQL Advanced Functions

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

Technical Details

Examples:-

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

SELECT BIN(2);

Result:

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:

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:

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:

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:

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

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

Technical Details

Examples:-

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

SELECT BINARY "simmanchith.com";

Result:

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:

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:

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:

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

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

Technical Details

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:

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:

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:

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:

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.

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

Technical Details

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:

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:

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:

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:

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

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

Technical Details

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:

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:

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

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

Examples:-

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

SELECT CONNECTION_ID();

Result:

This result will show your systems current connection.

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

Technical Details

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:

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:

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:

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

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

Technical Details

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:

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:

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:

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

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

Examples:-

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

SELECT CURRENT_USER();

Result:

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

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

Examples:-

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

SELECT VERSION();

Result:

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

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

Examples:-

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

SELECT USER();

Result:

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

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

Examples:-

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

SELECT SYSTEM_USER();

Result:

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

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

Examples:-

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

SELECT SESSION_USER();

Result:

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

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

Technical Details

Examples:-

Example 1: This example to compare two expressions:

SELECT NULLIF(45, "India");

Result:

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:

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:

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.

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

Technical Details

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:

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:

From the above table 3 data's inserted lastly.

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

Technical Details

Examples:-

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

SELECT ISNULL("");

Result:

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:

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.

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

Technical Details

Examples:-

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

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

Result:

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:

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

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

Technical Details

Examples:-

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

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

Result:

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:

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:

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:

SELECT 
StudentName, Sub1, 
IF(Sub1>50, "MORE", "LESS") AS ConditionResult
FROM STUDENT;

Result:

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."

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

Examples:-

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

SELECT DATABASE();

Result:

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