MySQL CHARACTER_LENGTH() Function

The CHARACTER_LENGTH() function in MySQL is used to determine the number of characters in a given string. This function is identical to the CHAR_LENGTH() function and provides the same functionality.

Syntax

CHARACTER_LENGTH(string)

Parameters

Definition and Use

• Purpose: Calculates and returns the number of characters in a string.
• Behavior: Counts characters, not bytes, making it suitable for multibyte character sets.
• Equivalent: The CHARACTER_LENGTH() function is functionally identical to the CHAR_LENGTH() function.

Usage Example 1: Simple String Length

To determine the length of the string "SQL Tutorial":

SELECT CHARACTER_LENGTH("SQL Tutorial") AS LengthOfString;

Output:

Usage Example 2: Column Data

To calculate the length of the text in the CustomerName column from the Customers table:

SELECT CHARACTER_LENGTH(CustomerName) AS LengthOfName
FROM Customers;

Output Explanation:

• For a value like "Bob", the result will be 3 because there are 3 characters in the name.

Technical Details

• Availability: Available from MySQL 4.0 onwards.
• Return Type: Returns an integer value representing the number of characters.

Applications

1. Data Validation: Ensure string fields meet specific length requirements.
2. Sorting and Filtering: Sort or filter records based on string lengths.
3. Data Preparation: Adjust or validate string lengths during data import or export processes.

Example Outputs

Suppose the Customers table contains the following data:

After running the query:

SELECT CHARACTER_LENGTH(CustomerName) AS LengthOfName
FROM Customers;

The result will be:

Key Notes

• The CHARACTER_LENGTH() function provides the same results as CHAR_LENGTH(), giving you flexibility in choosing the preferred naming convention.
• Unlike the LENGTH() function, which counts bytes, CHARACTER_LENGTH() counts the number of characters, making it ideal for multibyte string handling.

With the CHARACTER_LENGTH() function, managing and analyzing string lengths in MySQL becomes simple and efficient, ensuring that your queries handle text data accurately.