How the CHAR() function works in Mariadb?

The CHAR() function is a string function that returns a string value composed of one or more characters based on their numeric codes.

Posted on

The CHAR() function is a string function that returns a string value composed of one or more characters based on their numeric codes. The CHAR() function can be used to create special characters, such as control characters, escape characters, or non-printable characters.

Syntax

The syntax of the CHAR() function is as follows:

CHAR(N1[, N2, ...] [USING charset_name])

Where:

  • N1, N2, … are integer values that represent the numeric codes of the characters to be returned. The numeric codes are based on the ASCII or Unicode standard, depending on the character set used.
  • charset_name is an optional argument that specifies the name of the character set to use. If not specified, the default character set of the connection is used.

The return type of the function is a string value.

Examples

Example 1: Creating a string from ASCII codes

In this example, we use the CHAR() function to create a string from the ASCII codes of the characters. We use the SELECT statement to display the result.

SELECT CHAR(72, 101, 108, 108, 111) AS string;

The output is:

+-------+
| string|
+-------+
| Hello |
+-------+

Example 2: Creating a string from Unicode codes

In this example, we use the CHAR() function to create a string from the Unicode codes of the characters. We use the USING argument to specify the utf8mb4 character set. We use the SELECT statement to display the result.

SELECT CHAR(128512 USING utf8mb4) AS string;

The output is:

+--------+
| string |
+--------+
| 😀     |
+--------+

Example 3: Creating a control character

In this example, we use the CHAR() function to create a control character. We use the ASCII code 9, which represents the horizontal tab character. We use the SELECT statement to display the result.

SELECT CONCAT('Hello', CHAR(9), 'World') AS string;

The output is:

+--------------+
| string       |
+--------------+
| Hello	World |
+--------------+

Some other functions that are related to the CHAR() function are:

  • ASCII(): Returns the ASCII code of the first character of a string. The syntax is ASCII(string).
  • UNICODE(): Returns the Unicode code of the first character of a string. The syntax is UNICODE(string).
  • ORD(): Returns the numeric code of the leftmost character of a string. The syntax is ORD(string).
  • CHARSET(): Returns the name of the character set of a string. The syntax is CHARSET(string).

For example, you can use the ASCII() function to get the ASCII code of the first character of a string, which is the opposite of the CHAR() function:

SELECT ASCII('Hello') AS code;

The output is:

+------+
| code |
+------+
| 72   |
+------+

Conclusion

The CHAR() function is a useful function to create a string value composed of one or more characters based on their numeric codes. The CHAR() function takes one or more integer values as arguments, and returns a string value that contains the corresponding characters. The numeric codes are based on the ASCII or Unicode standard, depending on the character set used. The CHAR() function can also take an optional argument that specifies the name of the character set to use. The CHAR() function can be used to create special characters, such as control characters, escape characters, or non-printable characters. The CHAR() function can be combined with other functions to perform various string operations and analyses.