How the CHARSET() function works in Mariadb?

The CHARSET() function is a string function that returns the name of the character set of a given string. The character set of a string determines how the string is stored, displayed, and compared. The CHARSET() function can be used to get the character set information of a string, or to perform various operations based on the character set of a string.

Syntax

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

CHARSET(string)

Where:

• string is an expression that returns a string value to be examined.

The return type of the function is a string value.

Examples

Example 1: Getting the character set of a string

In this example, we use the CHARSET() function to get the character set of a string. We use the SELECT statement to display the result.

SELECT CHARSET('Hello') AS charset;

The output is:

+---------+
| charset |
+---------+
| utf8mb4 |
+---------+

Example 2: Getting the character set of a column value

In this example, we use the CHARSET() function to get the character set of a column value. We use the products table as an example, which has the following structure and data:

DROP TABLE IF EXISTS products;
CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50) CHARACTER SET utf8mb4,
  price DECIMAL(10,2)
);

INSERT INTO products VALUES
(1, 'Laptop', 999.99),
(2, 'Mouse', 19.99),
(3, 'Keyboard', 49.99),
(4, 'Monitor', 199.99),
(5, 'Speaker', 29.99);

We use the SELECT statement to display the product name and the character set of the name.

SELECT name, CHARSET(name) AS charset
FROM products;

The output is:

+----------+---------+
| name     | charset |
+----------+---------+
| Laptop   | utf8mb4 |
| Mouse    | utf8mb4 |
| Keyboard | utf8mb4 |
| Monitor  | utf8mb4 |
| Speaker  | utf8mb4 |
+----------+---------+

Example 3: Using the CHARSET() function in a WHERE clause

In this example, we use the CHARSET() function in a WHERE clause to filter the rows based on the character set of a column value. We use the products table as an example, and we want to select only the products that have a name with the utf8mb4 character set.

SELECT name, price
FROM products
WHERE CHARSET(name) = 'utf8mb4';

The output is:

+----------+--------+
| name     | price  |
+----------+--------+
| Laptop   | 999.99 |
| Mouse    |  19.99 |
| Keyboard |  49.99 |
| Monitor  | 199.99 |
| Speaker  |  29.99 |
+----------+--------+

Related Functions

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

• COLLATION(): Returns the name of the collation of a given string. The collation of a string determines how the string is sorted and compared. The syntax is COLLATION(string).
• CONVERT(): Returns a string value converted to a specified character set. The syntax is CONVERT(string USING charset_name).
• CHARACTER_SET_NAME(): Returns the name of the default character set of the current connection. The syntax is CHARACTER_SET_NAME().
• COLLATION_NAME(): Returns the name of the default collation of the current connection. The syntax is COLLATION_NAME().

For example, you can use the COLLATION() function to get the collation of a string, which is related to the character set of the string:

SELECT COLLATION('Hello') AS collation;

The output is:

+--------------------+
| collation          |
+--------------------+
| utf8mb4_general_ci |
+--------------------+

Conclusion

The CHARSET() function is a useful function to get the name of the character set of a given string. The character set of a string determines how the string is stored, displayed, and compared. The CHARSET() function takes a string expression as an argument, and returns a string value that is the name of the character set of the argument. The CHARSET() function can be used in various contexts, such as in SELECT, UPDATE, DELETE, WHERE, ORDER BY, and GROUP BY clauses. The CHARSET() function can be combined with other functions to perform various string operations and analyses.