O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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;

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required