MySQL CHARACTER_LENGTH() Function

 The CHARACTER_LENGTH() function in MySQL is used to return the number of characters in a string. It counts the characters in the string, not the bytes, which makes it useful for working with multi-byte character sets.

Syntax:

CHARACTER_LENGTH(string)

Parameters:

• string: The string whose character count is to be determined.

Key Points:

1. If the string is NULL, the function returns NULL.
2. This function differs from LENGTH():
   • CHARACTER_LENGTH() counts characters.
   • LENGTH() counts bytes.

Examples:

1. Counting characters in a simple string:

SELECT CHARACTER_LENGTH('MySQL') AS char_count;
-- Output: 5

2. Counting characters in a string with spaces:

SELECT CHARACTER_LENGTH('Hello World!') AS char_count;
-- Output: 12

3. Handling multi-byte characters:

If the string contains multi-byte characters (e.g., UTF-8):

SELECT CHARACTER_LENGTH('Hello') AS char_count;
-- Output: 5

4. NULL input:

SELECT CHARACTER_LENGTH(NULL) AS char_count;
-- Output: NULL

5. Comparison with LENGTH():

SELECT CHARACTER_LENGTH('Hello') AS char_count, LENGTH('Hello') AS byte_count;
-- Output: char_count = 2, byte_count = 6 (assuming UTF-8 encoding)

Use Cases:

• Determining the number of characters in a text column for validations.
• Handling strings in multi-lingual databases.
• Differentiating between character and byte counts in storage-sensitive applications.

Would you like further examples or assistance with a specific query?