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

A string function that returns the number of characters in a string expression.
Synopsis
LENGTH(string-expression)

{fn 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
LENGTH returns an integer that denotes the number of characters, not the number of bytes, of the given string expression. The string-expression can be a string (from which trailing blanks are removed), or a number (which Caché converts to canonical form).
Note that LENGTH can be used as an ODBC scalar function (with the curly brace syntax) or as an SQL general function.
LENGTH and the other length functions ($LENGTH, CHARACTER_LENGTH, CHAR_LENGTH, and DATALENGTH) all perform the following operations:
LENGTH differs from the other length functions ($LENGTH, CHARACTER_LENGTH, CHAR_LENGTH, and DATALENGTH) when performing the following operations:
Examples
In the following example, Caché first converts each number to canonical form (removing leading and trailing zeros, resolving leading signs, and removing a trailing decimal separator character). Each LENGTH returns a length of 1:
SELECT {fn LENGTH(7.00)} AS CharCount,
       {fn LENGTH(+007)} AS CharCount,
       {fn LENGTH(007.)} AS CharCount,
       {fn LENGTH(00000.00)} AS CharCount,
       {fn LENGTH(-0)} AS CharCount
 
In the following example, the first LENGTH removes the leading zero, returning a length value of 2; the second LENGTH treats the numeric value as a string, and does not remove the leading zero, returning a length value of 3:
SELECT LENGTH(0.7) AS CharCount,
       LENGTH('0.7') AS CharCount
 
The following example returns the value 12:
SELECT LENGTH('INTERSYSTEMS') AS CharCount
 
The following example shows how LENGTH handles leading and trailing blanks. The first LENGTH returns 15, because LENGTH excludes trailing blanks, but not leading blanks. The second LENGTH returns 12, because LTRIM excludes the leading blanks:
SELECT LENGTH('   INTERSYSTEMS      ') AS CharCount,
       LENGTH(LTRIM('   INTERSYSTEMS      ')) AS CharCount
 
The following example returns the number of characters in each Name value in the Sample.Person table:
SELECT Name,{fn LENGTH(Name)} AS CharCount
FROM Sample.Person
ORDER BY CharCount
 
The following example returns the number of characters in the DOB (date of birth) field. Note that the length returned (by LENGTH, CHAR_LENGTH, and CHARACTER_LENGTH) is the internal ($HOROLOG) format of the date, not the display format. The display length of DOB is ten characters; all three length functions return the internal length of 5:
SELECT DOB,{fn LENGTH(DOB)} AS LenCount,
CHAR_LENGTH(DOB) AS CCount,
CHARACTER_LENGTH(DOB) AS CtrCount
FROM Sample.Person
 
The following Embedded SQL example gives the length of a string of Unicode characters. The length returned is the number of characters (7), not the number of bytes.
  IF $SYSTEM.Version.IsUnicode() {
    SET a=$CHAR(920,913,923,913,931,931,913)
    &sql(SELECT LENGTH(:a) INTO :b )
    IF SQLCODE'=0 {
       WRITE !,"Error code ",SQLCODE }
    ELSE {
       WRITE !,"The Greek Sea: ",a,!,$LENGTH(a),!,b }
  }
  ELSE {WRITE "This example requires a Unicode installation of Caché"}
 
(Note that the above example requires a Unicode installation of Caché.)
See Also