Caché SQL Reference
CHARACTER_LENGTH
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

A function that returns the number of characters in an expression.
Synopsis
CHARACTER_LENGTH(expression)
Arguments
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.
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
 
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
 
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 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 Embedded SQL example demonstrates how CHARACTER_LENGTH handles Unicode characters. CHARACTER_LENGTH counts the number of characters, regardless of their byte length.
  IF $SYSTEM.Version.IsUnicode() {
    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 }
  }
  ELSE {WRITE "This example requires a Unicode installation of Caché"}
 
returns 5.
See Also