LENGTH (SQL)
Synopsis
LENGTH(string-expression)
{fn LENGTH(string-expression)}
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 InterSystems IRIS 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 returns the length of the Logical (internal data storage) value of a field, not the display value, regardless of the SelectMode setting. All SQL functions always use the internal storage value of a field.
-
LENGTH returns the length of the canonical form of a number. A number in canonical form excludes leading and trailing zeros, leading signs (except a single minus sign), and a trailing decimal separator character. LENGTH returns the string length of a numeric string. A numeric string is not converted to canonical form.
-
LENGTH does not exclude leading blanks from strings. You can remove leading blanks from a string using the LTRIM function.
LENGTH differs from the other length functions ($LENGTH, CHARACTER_LENGTH, CHAR_LENGTH, and DATALENGTH) when performing the following operations:
-
LENGTH excludes trailing blanks and the string-termination character.
$LENGTH, CHARACTER_LENGTH, CHAR_LENGTH, and DATALENGTH do not exclude trailing blanks and terminators.
-
LENGTH returns NULL if passed a NULL value, and 0 if passed an empty string.
CHARACTER_LENGTH, CHAR_LENGTH, and DATALENGTH also return NULL if passed a NULL value, and 0 if passed an empty string. $LENGTH returns 0 if passed a NULL value, and 0 if passed an empty string.
-
LENGTH does not support data stream fields. Specifying a stream field for string-expression results in an SQLCODE -37.
$LENGTH also does not support stream fields. CHARACTER_LENGTH, CHAR_LENGTH, and DATALENGTH functions do support data stream fields.
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).
Examples
In the following example, InterSystems IRIS 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.
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 }
See Also
-
SQL functions: CHAR_LENGTH, CHARACTER_LENGTH, DATALENGTH, LEN, $LENGTH
-
ObjectScript function: $LENGTH