The OCTET_LENGTH function
The OCTET_LENGTH function returns the number of bytes and generally includes trailing white space characters 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 SPL variable. The actual behavior of OCTET_LENGTH 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 character. For simplicity,
the Example column also assumes that the example strings consist of
single-byte characters.
| OCTET_LENGTH argument | Behavior | Example |
|---|---|---|
| Quoted string | Returns number of bytes in string, including any trailing white- space characters. | 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 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 'Ludwigsss', the result is still 15. |
| VARCHAR or NVARCHAR column | Returns number of bytes in string, including trailing white space. Value is the actual length, in bytes, of the character string, not the declared 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. |
| TEXT column | Returns number of bytes 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 bytes that the variable contains, including any trailing white space, 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 space in the byte count, whereas LENGTH generally excludes trailing white space 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. This advantage of the OCTET_LENGTH function applies both to single-byte code sets such as ISO8859-1 and multibyte code sets.
| OCTET_LENGTH input string | Description | Result |
|---|---|---|
| 'abc ' | A quoted string with four single-byte characters
(the characters abc and one trailing space) |
4 |
| 'A1A2B1B2' | A quoted string with two multibyte characters | 4 |
| 'aA1A2bB1B2' | A quoted string with two single-byte and two multibyte characters | 6 |