Name
BIT_LENGTH, CHAR_LENGTH, and OCTET_LENGTH
Synopsis
The closest any of the vendors get to the BIT_LENGTH function is Oracle. Oracle supports the LENGTHB function, which returns an integer value representing the number of bytes in an expression.
MySQL and PostgreSQL support CHAR_LENGTH and the SQL99 synonym CHARACTER_LENGTH( ). PostgreSQL also supports EXTRACT( ), OCTET_LENGTH( ), and POSITION( ) as per the SQL99 standard. The other two vendors each have a similar function that provides identical functionality. SQL Server provides the LEN function and Oracle provides the LENGTH function.
MySQL and PostgreSQL also fully support the OCTET_LENGTH function.
Example
The following example 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 SQL Server */ SELECT DATALENGTH(title) FROM titles WHERE type = 'popular_comp' GO /* On Oracle */ SELECT LENGTH('HORATIO') "Length of characters" FROM dual;
Get SQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.