The CHAR_LENGTH function
The CHAR_LENGTH function (also known as the CHARACTER_LENGTH function) returns the number of characters in a quoted string, column with a character data type, host variable, or procedure variable. However, the actual behavior of this function varies with the type of argument that the user specifies.
The following table shows how the CHAR_LENGTH function
operates on each of the argument types. The Example column
in this table uses the symbol s to represent a single-byte
trailing white space. For simplicity, the Example column assumes
that the strings consist of single-byte characters.
| CHAR_LENGTH argument | Behavior | Example |
|---|---|---|
| Quoted string | Returns number of characters in string, including any trailing white- space (as defined in the locale). | If the string is 'Ludwig', the result is 6. If the string is 'Ludwigssss', the result is 10. |
| CHAR or NCHAR column | Returns number of characters in string, including trailing white space characters. This value is the defined length, in bytes, of the column. | If the fname column of the customer table is a CHAR(15) column, and this column contains the string 'Ludwig', the result is 15. If the fname column contains the string 'Ludwigssss', the result is 15. |
| VARCHAR or NVARCHAR column | Returns number of characters in string, including white space characters. Value is the actual length, in bytes, of the string, not the declared maximum column size. | If the cat_advert column of the catalog table is a VARCHAR(255, 65), and this column contains the string "Ludwig", the result is 6. If the column contains the string 'Ludwigssss', the result is 10. |
| TEXT column | Returns number of characters in column, including trailing white space characters. | If the cat_descr column in the catalog table is a TEXT column, and this column contains the string 'Ludwig', the result is 6. If the cat_descr column contains the string 'Ludwigssss', the result is 10. |
| Host or procedure variable | Returns number of characters that the variable contains, including any trailing white space, regardless of declared length of the variable. | If the procedure variable f_name is defined as CHAR(15), and this variable contains the string 'Ludwig', the result is 6. If the f_name variable contains the string 'Ludwigssss', the result is 10. |
The CHAR_LENGTH function is especially useful with multibyte code sets. If a quoted string of characters contains any multibyte characters, the number of characters in the string differs from the number of bytes in the string. You can use the CHAR_LENGTH function to determine the number of characters in the quoted string.
However, the CHAR_LENGTH function can also be useful in single-byte code sets. In these code sets, the number of bytes in a column is equal to the number of characters in the column. If you use the LENGTH function to determine the number of bytes in a column (which is equal to the number of characters in this case), LENGTH trims the column values and returns the length of the trimmed string. In contrast, CHAR_LENGTH does not trim the column values but returns the declared size of the column.
| CHAR_LENGTH input string | Description | Result |
|---|---|---|
| 'abc ' | A quoted string with 4 single-byte characters (the characters abc and 1 trailing space) | 4 |
| 'A1A2B1B2' | A quoted string with 2 multibyte characters | 2 |
| 'aA1A2B1B2' | A quoted string with 2 single-byte and 2 multibyte characters | 4 |