The LENGTH function

The LENGTH function returns the number of bytes of data in character data.

However, the behavior of the LENGTH function varies with the type of argument that the user specifies. The argument can be a quoted string, a character-type column other than the TEXT data type, a TEXT column, a host variable, or an SPL routine variable.

The following table shows how the LENGTH function operates on each of these argument types. The Example column in this table uses the symbol s to represent a single-byte trailing white space character.

This table assumes that all arguments consist of single-byte characters.

LENGTH argument Behavior Example
Quoted string Returns number of bytes in string, minus 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 still 6.
CHAR, VARCHAR, LVARCHAR, NCHAR, or NVARCHAR column Returns number of bytes in a column, minus any trailing white- space characters, regardless of defined length 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 6. If the fname column contains the string 'Ludwigssss', the result is still 6.
TEXT column Returns number of bytes in a 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 bytes that the variable contains, minus any trailing white pace, regardless of defined 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 still 6.
When you use the default locale or any locale with a single-byte code set, the LENGTH function seems to return the number of characters in the column. In the following example, the stores_demo database, which contains the customer table, uses the default code set for the U.S. English locale. Suppose a user enters a SELECT statement with the LENGTH function to display the family name, length of the family name, and customer number for rows where the customer number is less than 106.
SELECT lname AS cust_name, 
   length (fname) AS length, customer_num AS cust_num
   FROM customer WHERE customer_num < 106

The following example of output shows the result of the query. For each row that is retrieved, the length column seems to show the number of characters in the lname (cust_name) column. However, the length column actually displays the number of bytes in the lname column.

In the default code set, one byte stores one character. For more information about the default code set, see The default locale.
cust_name length cust_num
Ludwig 6 101
Carole 6 102
Philip 6 103
Anthony 7 104
Raymond 7 105

When you use the LENGTH function in a locale that supports a multibyte code set, such as the Japanese SJIS code set, the distinction between characters and bytes is meaningful. When you use the LENGTH function in a locale that supports a multibyte code set, the distinction between characters and bytes is meaningful.LENGTH returns the number of bytes in its argument. This result might be different from the number of characters.

The next example assumes that the database that contains the customer_multi table has locale with a multibyte code set. Suppose that the user enters a SELECT statement with the LENGTH function to display lname, its length, and customer_num for the customer whose number is 199.
SELECT lname AS cust_name, 
   length (fname) AS length, customer_num AS cust_num
   FROM customer_multi WHERE customer_num = 199
Suppose that lname for customer 199 consists of four characters: 
aA1A2bB1B2

In this representation, the first character (the symbol a) is a single-byte character. The second character (the symbol A1A2) is a 2-byte character. The third character (the symbol b) is a single-byte character. The fourth character (the symbol B1B2) is a 2-byte character.

The following example of output shows the result of the query. Although the customer given name consists of four characters, the length column shows that the total number of bytes in this name is 6.
cust_name length cust_num
aA1A2bB1B2 6 199