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)) Expr8FROM /shared/examples/ds_orders/customers customers