LENGTH
LENGTH returns the number of characters (rather than the number of bytes) in a given string expression.
Syntax
LENGTH (string)
Remarks
• CHAR_LENGTH and CHARACTER_LENGTH are synonymous with LENGTH.
• If the input is NULL, the output is also NULL. Otherwise, the output is an integer that is equal to or greater than zero.
• If the input is an empty string, the output is zero.
• The length of a white-space in an input argument is counted as 1 (one).
• If you want to count the white-space included in an input string, use the CONCAT function to accommodate the space, as in this example:
LENGTH (CONCAT (customers.ContactFirstName, CONCAT (' ', customers.ContactLastName)))
• If you want to find the length of an integer, you must convert the integer to VARCHAR and then pass the string as the input for the LENGTH function.
For example, if you want to find out the number of digits in a phone number, cast the phone number’s integer into a VARCHAR and use it in the LENGTH function.
The following table lists the input types that you can use in LENGTH, and their corresponding output types.
Data Type of string | Output Type |
BLOB, CHAR, CLOB, LONGVARCHAR, VARCHAR | INTEGER |
NULL | NULL |
Example
SELECT LENGTH (customers.PostalCode) Expr1,
LENGTH (NULL) Expr2,
LENGTH (' ') Expr3,
LENGTH ('') Expr4,
LENGTH (CONCAT(customers.ContactFirstName,
CONCAT(' ', customers.ContactLastName))) Expr5,
LENGTH (customers.FaxNumber) Expr6,
LENGTH (TO_CHAR(1000)) Expr7,
LENGTH (CAST (customers.PhoneNumber AS VARCHAR)) Expr8
FROM /shared/examples/ds_orders/customers customers