CHARACTER_LENGTH

A general SQL function that returns the number of characters in a string expression.

Synopsis

CHARACTER_LENGTH(string-expression)

Arguments

string-expression

A string expression, which can be the name of a column, a string literal, or the result of another scalar function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR).

Description

CHARACTER_LENGTH returns an integer that denotes the number of characters, not the number of bytes, of the given string expression. This count 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 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 blanks), the second CHARACTER_LENGTH returns 7.

SELECT DISTINCT CHARACTER_LENGTH(007.000) AS NumLen,
     CHARACTER_LENGTH('007.000') AS NumStringLen
     FROM Sample.Employee

The CHARACTER_LENGTH and CHAR_LENGTH functions are identical.

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

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

The following example uses embedded SQL to demonstrate 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
   FROM Sample.Person)
   WRITE !,"Error code ",SQLCODE
   WRITE !,"The CHARACTER length is ",b

returns 5.