How the CHAR_LENGTH() function works in Mariadb?

The CHAR_LENGTH() function is a string function that returns the number of characters in a given string.

Posted on

The CHAR_LENGTH() function is a string function that returns the number of characters in a given string. The CHAR_LENGTH() function can be used to measure the length of a string, or to perform various operations based on the length of a string.

Syntax

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

CHAR_LENGTH(string)

Where:

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

The return type of the function is an integer value.

Examples

Example 1: Getting the length of a string

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

SELECT CHAR_LENGTH('Hello') AS length;

The output is:

+--------+
| length |
+--------+
|      5 |
+--------+

Example 2: Getting the length of a column value

In this example, we use the CHAR_LENGTH() function to get the length 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),
  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 length of the name.

SELECT name, CHAR_LENGTH(name) AS length
FROM products;

The output is:

+----------+--------+
| name     | length |
+----------+--------+
| Laptop   |      6 |
| Mouse    |      5 |
| Keyboard |      8 |
| Monitor  |      7 |
| Speaker  |      7 |
+----------+--------+

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

In this example, we use the CHAR_LENGTH() function in a WHERE clause to filter the rows based on the length 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 more than 6 characters.

SELECT name, price
FROM products
WHERE CHAR_LENGTH(name) > 6;

The output is:

+----------+--------+
| name     | price  |
+----------+--------+
| Keyboard |  49.99 |
| Monitor  | 199.99 |
| Speaker  |  29.99 |
+----------+--------+

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

  • LENGTH(): Returns the number of bytes in a given string. The syntax is LENGTH(string).
  • BIT_LENGTH(): Returns the number of bits in a given string. The syntax is BIT_LENGTH(string).
  • SUBSTRING(): Returns a substring of a given string. The syntax is SUBSTRING(string, start, length).
  • CONCAT(): Returns a string that is the concatenation of two or more strings. The syntax is CONCAT(string1, string2, ..., stringN).

For example, you can use the LENGTH() function to get the number of bytes in a string, which may be different from the number of characters if the string contains multibyte characters:

SELECT CHAR_LENGTH('你好') AS char_length, LENGTH('你好') AS byte_length;

The output is:

+-------------+-------------+
| char_length | byte_length |
+-------------+-------------+
| 2           | 6           |
+-------------+-------------+

Conclusion

The CHAR_LENGTH() function is a useful function to get the number of characters in a given string. The CHAR_LENGTH() function takes a string expression as an argument, and returns an integer value that is the length of the string. The CHAR_LENGTH() function can be used in various contexts, such as in SELECT, UPDATE, DELETE, WHERE, ORDER BY, and GROUP BY clauses. The CHAR_LENGTH() function can be combined with other functions to perform various string operations and analyses.