SQL in a Nutshell, 3rd Edition

Name

BIT_LENGTH, CHAR_LENGTH, and OCTET_LENGTH

All platforms stray from the ANSI standard in their support for scalar functions for determining the length of expressions. While the platform support is nonstandard, the equivalent functionality exists under different names.

ANSI SQL Standard Syntax

The ANSI SQL scalar functions for getting the length of a value take an expression to calculate the value and return the length as an integer. The BIT_LENGTH function returns the number of bits contained within the value of expression, CHAR_LENGTH returns the number of characters in the string expression, and OCTET_LENGTH returns the number of octets in the string expression. All three of these functions will return NULL if expression is NULL:

BIT_LENGTH( expression )
CHAR_LENGTH( expression )
OCTET_LENGTH( expression )

MySQL and PostgreSQL

MySQL and PostgreSQL both support BIT_LENGTH, CHAR_LENGTH, OCTET_LENGTH, and the ANSI SQL synonym CHARACTER_LENGTH.

Oracle

Oracle supports the LENGTHB function, which returns an integer value representing the number of bytes in an expression. For the length of an expression in characters, Oracle provides a LENGTH function as a synonym for CHAR_LENGTH.

SQL Server

SQL Server provides LEN, which performs all three functions.

Example

The following example, shown for different databases, determines the length of a string and a value retrieved from a column:

/* On MySQL and PostgreSQL */
SELECT CHAR_LENGTH('hello');
SELECT OCTET_LENGTH(book_title) FROM titles; /* On Microsoft ...