Skip to main content
Previous sectionNext section

CHARACTER_LENGTH

A function that returns the number of characters in an expression.

Synopsis

CHARACTER_LENGTH(expression)

Arguments

Argument Description
expression An expression, which can be the name of a column, a string literal, or the result of another scalar function. The underlying data type can be a character type (such as CHAR or VARCHAR), a numeric, or a data stream.

CHARACTER_LENGTH returns the INTEGER data type.

Description

CHARACTER_LENGTH returns an integer value representing the number of characters, not the number of bytes, in the specified expression. The expression can be a string, or any other data type such as a numeric or a data stream field. This integer count returned including leading and trailing blanks and the string-termination character. CHARACTER_LENGTH returns NULL if passed a NULL value, and 0 if passed an empty string ('') value.

Numbers are parsed to canonical form before counting the characters; quoted number strings are not parsed. In the following example, the first CHARACTER_LENGTH returns 1 (because number parsing removes leading and trailing zeros), the second CHARACTER_LENGTH returns 8.

SELECT CHARACTER_LENGTH(007.0000) AS NumLen,
       CHARACTER_LENGTH('007.0000') AS NumStringLen
Copy code to clipboard
Note:

The CHARACTER_LENGTH, CHAR_LENGTH, and DATALENGTH functions are identical. All of them accept a stream field argument. The LENGTH and $LENGTH functions do not accept a stream field argument.

LENGTH also differs from these functions by stripping trailing blanks and the string-termination character before counting characters. $LENGTH also differs from these functions because it returns 0 if passed a NULL value, and 0 if passed an empty string.

Examples

The following example returns the number of characters in the state abbreviation field (Home_State) in the Sample.Employee table. (All U.S. states have a two-letter postal abbreviation):

SELECT DISTINCT CHARACTER_LENGTH(Home_State) AS StateLength
     FROM Sample.Employee
Copy code to clipboard

The following example returns the names of the employees and the number of characters in each employee name, ordered by ascending number of characters:

SELECT Name,
     CHARACTER_LENGTH(Name) AS NameLength
     FROM Sample.Employee
     ORDER BY NameLength
Copy code to clipboard

The following examples return the number of characters in a character stream field (Notes) and a binary stream field (Picture) in the Sample.Employee table:

SELECT DISTINCT CHARACTER_LENGTH(Notes) AS NoteLen
     FROM Sample.Employee WHERE Notes IS NOT NULL
Copy code to clipboard
SELECT DISTINCT CHARACTER_LENGTH(Picture) AS PicLen
     FROM Sample.Employee WHERE Picture IS NOT NULL
Copy code to clipboard

The following Embedded SQL example demonstrates how CHARACTER_LENGTH handles Unicode characters. CHARACTER_LENGTH counts the number of characters, regardless of their byte length:

    SET a=$CHAR(960)_"FACE"
    WRITE !,a
    &sql(SELECT CHARACTER_LENGTH(:a) INTO :b)
    IF SQLCODE'=0 {WRITE !,"Error code ",SQLCODE }
    ELSE {WRITE !,"The CHARACTER length is ",b }
Copy code to clipboard

returns 5.

See Also