A function that returns the number of characters in an
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
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.
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
The following example returns the names of the employees and
the number of characters in each employee name, ordered by ascending
number of characters:
CHARACTER_LENGTH(Name) AS NameLength
ORDER BY NameLength
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
SELECT DISTINCT CHARACTER_LENGTH(Picture) AS PicLen
FROM Sample.Employee WHERE Picture IS NOT NULL
The following example demonstrates how CHARACTER_LENGTH handles Unicode characters. CHARACTER_LENGTH counts the number of characters, regardless of their byte length: