Length Functions

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page Data Types and Expressions > Expression >

Length Functions

Use length functions to determine the length of a column, string, or variable.

Length Functions:
 
    (1)
|---------+-LENGTH---------------+------------------------------>
          +-+-CHAR_LENGTH------+-+
          | '-CHARACTER_LENGTH-' |
          '-OCTET_LENGTH---------'
 
                          (2)
>--(--+-| Quoted String |-----------+--)------------------------|
      |  (3)    (4)                 |
      +---------------variable_name-+
      '-+--------+--column----------'
        '-table.-'

Notes:

Informix extension
See page Quoted String
ESQL/C
Stored Procedure Language

Element	Description	Restrictions	Syntax
column	Name of a column in table	Must have a character data type	Identifier, p. Identifier
table	Name of the table in which the specified column occurs	Must exist	Database Object Name, p. Database Object Name
variable	Host variable or SPL variable that contains a character string	Must have a character data type	See language-specific rules for names.

Each of these functions has a distinct purpose:

LENGTH
OCTET_LENGTH
CHAR_LENGTH (also known as CHARACTER_LENGTH)

The LENGTH Function

The LENGTH function returns the number of bytes in a character column, not including any trailing blank spaces. For BYTE or TEXT columns, LENGTH returns the full number of bytes, including any trailing blank spaces.

In ESQL/C, LENGTH can also return the length of a character variable.

The next example illustrates the use of the LENGTH function:

SELECT customer_num, LENGTH(fname) + LENGTH(lname),
   LENGTH('How many bytes is this?')
   FROM customer WHERE LENGTH(company) > 10

See also the discussion of LENGTH in the IBM Informix GLS User's Guide.

The OCTET_LENGTH Function

OCTET_LENGTH returns the number of bytes in a character column, including any trailing spaces. See also the IBM Informix GLS User's Guide.

The CHAR_LENGTH Function

The CHAR_LENGTH function (also called CHARACTER_LENGTH) returns the number of logical characters (which can be distinct from the number of bytes in some East Asian locales) in a character column value. For a discussion of this function, see the IBM Informix GLS User's Guide.