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 a stored-procedure 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. This table also assumes that the sample strings consist of single-byte characters.
s
Quoted string
Returns number of bytes in string, minus any trailing white spaces as defined in the locale.
If the string is 'Ludwig', the result is 6. If the string is 'Ludwigssss', the result is still 6.
'Ludwig'
'Ludwigssss'
CHAR, VARCHAR, NCHAR, or NVARCHAR column
Returns number of bytes in a column, minus any trailing white spaces, regardless of defined length of 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 spaces.
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 contained in the variable, minus any trailing white spaces, 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.
With Single-Byte Code Sets 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 stores7 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 last name, length of the last name, and customer number for rows where the customer number is less than 106.
Ludwig
6
101
Carole
102
Philip
103
Anthony
7
104
Raymond
105
With Multibyte Code Sets 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. The LENGTH function returns the number of bytes in the column or quoted string, and this result might be quite different from the number of characters in the string. The following example assumes that the database that contains the customer_multi table has a database locale that supports a multibyte code set. Suppose that the user enters a SELECT statement with the LENGTH function to display the last name, the length of the last name, and the customer number for the customer whose customer number is 199.
a
A
b
B
aA1A2bB1B2
199
The OCTET_LENGTH Function The OCTET_LENGTH function returns the number of bytes and generally includes trailing white spaces in the byte count. This SQL length function uses the definition of white space that the locale defines. OCTET_LENGTH returns the number of bytes in a character column, quoted string, host variable, or procedure variable. However, the actual behavior of the OCTET_LENGTH function varies with the type of argument that the user specifies. The following table shows how the OCTET_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 also assumes that the sample strings consist of single-byte characters.
Returns number of bytes in string, including any trailing white spaces.
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 bytes in string, including trailing white spaces. 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 'Ludwigsss', the result is still 15.
Ludwigsss
VARCHAR or NVARCHAR column
Returns number of bytes in string, including trailing white spaces. This value is the actual length, in bytes, of the character string. It is not the defined maximum column size.
If the cat_advert column of the catalog table is a VARCHAR(255, 65) column, and this column contains the string "Ludwig", the result is 6. If the column contains the string 'Ludwigssss', the result is 10.
Ludwigssss
Returns number of bytes in column, including trailing white spaces.
Returns number of bytes that the variable contains, including any trailing white spaces, regardless of defined length of 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 difference between the LENGTH and OCTET_LENGTH functions is that OCTET_LENGTH generally includes trailing white spaces in the byte count, whereas LENGTH generally excludes trailing white spaces from the byte count. The advantage of the OCTET_LENGTH function over the LENGTH function is that the OCTET_LENGTH function provides the actual column size whereas the LENGTH function trims the column values and returns the length of the trimmed string. This advantage of the OCTET_LENGTH function applies both to single-byte code sets such as ISO8859-1 and multibyte code sets such as the Japanese SJIS code set. The following table shows some results that the OCTET_LENGTH function might generate.
'abc '
A quoted string with 4 single-byte characters (the characters abc and 1 trailing space)
abc
'A1A2B1B2'
A quoted string with 2 multibyte characters
'aA1A2bB1B2'
A quoted string with 2 single-byte and 2 multibyte characters
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 also assumes that the sample strings consist of single-byte characters.
Returns number of characters in string, including any trailing white spaces as defined in the locale.
Returns number of characters in string, including trailing white spaces. 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.
Returns number of characters in string, including white spaces. This value is the actual length, in bytes, of the character string. It is not the defined 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.
Returns number of characters in column, including trailing white spaces.
Returns number of characters that the variable contains, including any trailing white spaces, regardless of defined length of variable.
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 actual size of the column. The following table shows some results that the CHAR_LENGTH function might generate for quoted strings.
