How the BINARY operator works in Mariadb?

The BINARY operator is a string operator that converts a string to a binary string. The BINARY operator can be used to compare strings in a case-sensitive manner, or to perform bitwise operations on strings.

Posted on

The BINARY operator is a string operator that converts a string to a binary string. The BINARY operator can be used to compare strings in a case-sensitive manner, or to perform bitwise operations on strings. For example, the BINARY operator can be used to compare the strings ‘A’ and ‘a’, which are different in binary.

Syntax

The syntax of the BINARY operator is as follows:

BINARY string

The string parameter can be any valid string expression, such as a column name, a literal value, or a function call. The string parameter is the string that you want to convert to a binary string. The string parameter cannot be NULL.

The BINARY operator returns a binary string value, which is the binary representation of the input string. If the input string is NULL, the BINARY operator returns NULL.

Examples

Here are some examples of using the BINARY operator in Mariadb:

Example 1: BINARY operator with literal strings

To use the BINARY operator with literal strings, you can simply pass the strings as the parameters. For example:

SELECT BINARY 'A' AS result;

This statement returns ‘\x41’ as the output, which is the binary representation of the string ‘A’.

Example 2: BINARY operator with column values

To use the BINARY operator with column values, you can use the column names as the parameters. For example, suppose you have a table called users that stores the username and the password of different users. You can use the BINARY operator to convert the password column to a binary string for each user. For example:

SELECT username, BINARY password AS binary_password
FROM users;

This statement returns the username and the binary password for each row in the users table. For example, if the table contains the following data:

username password
Alice abc123
Bob xyz789
Charlie pqr456

The statement returns the following output:

username binary_password
Alice \x61\x62\x63\x31\x32\x33
Bob \x78\x79\x7A\x37\x38\x39
Charlie \x70\x71\x72\x34\x35\x36

Example 3: BINARY operator with function calls

To use the BINARY operator with function calls, you can use the function calls as the parameters. For example, you can use the BINARY operator to convert the string that is returned by another string function. For example:

SELECT BINARY UPPER('a') AS result;

This statement returns ‘\x41’ as the output, which is the binary representation of the string ‘A’, which is the upper case of the string ‘a’.

Example 4: BINARY operator for case-sensitive comparison

To use the BINARY operator for case-sensitive comparison, you can use the BINARY operator before the strings that you want to compare. For example, you can use the BINARY operator to check if the strings ‘A’ and ‘a’ are equal in a case-sensitive manner. For example:

SELECT BINARY 'A' = BINARY 'a' AS result;

This statement returns FALSE as the output, because the strings ‘A’ and ‘a’ are different in binary.

Example 5: BINARY operator for bitwise operations

To use the BINARY operator for bitwise operations, you can use the BINARY operator before the strings that you want to perform bitwise operations on. For example, you can use the BINARY operator to perform bitwise AND, OR, or XOR operations on the strings ‘A’ and ‘a’. For example:

SELECT BINARY 'A' & BINARY 'a' AS result;

This statement returns ‘\x01’ as the output, which is the result of the bitwise AND operation on the binary strings ‘\x41’ and ‘\x61’.

Some of the functions that are related to the BINARY operator are:

  • The CAST() function, which converts a value to a specified data type. The CAST() function can be used to convert a string to a binary string, or vice versa. For example, CAST('A' AS BINARY) returns the same result as BINARY 'A'.
  • The CHAR() function, which returns the string representation of a given binary value. The CHAR() function can be used to convert a binary string to a normal string. For example, CHAR(0x41) returns ‘A’ as the output, which is the string representation of the binary value ‘\x41’.
  • The ORD() function, which returns the numeric code of the first character of a given string. The ORD() function can be used to get the decimal value of a binary string. For example, ORD('\x41') returns 65 as the output, which is the decimal value of the binary string ‘\x41’.

Conclusion

The BINARY operator is a useful operator that can help you convert a string to a binary string in Mariadb. You can use it with any valid string expression, such as a column name, a literal value, or a function call. The BINARY operator returns a binary string value, which is the binary representation of the input string. You can also use the BINARY operator for case-sensitive comparison or bitwise operations on strings. You can also use some other functions that are related to the BINARY operator, such as the CAST(), CHAR(), and ORD() functions.