How the CHARACTER_LENGTH() function works in Mariadb?

The CHARACTER_LENGTH() function is a string function that returns the number of characters in a given string. The CHARACTER_LENGTH() function is equivalent to the CHAR_LENGTH() function, and 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 CHARACTER_LENGTH() function is as follows:

CHARACTER_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 CHARACTER_LENGTH() function to get the length of a string. We use the SELECT statement to display the result.

SELECT CHARACTER_LENGTH('Hello') AS length;

The output is:

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

Example 2: Getting the length of a column value

In this example, we use the CHARACTER_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, CHARACTER_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 CHARACTER_LENGTH() function in a WHERE clause

In this example, we use the CHARACTER_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 CHARACTER_LENGTH(name) > 6;

The output is:

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

Related Functions

Some other functions that are related to the CHARACTER_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 CHARACTER_LENGTH('你好') AS char_length, LENGTH('你好') AS byte_length;

The output is:

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

Conclusion

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