SQL OCTET_LENGTH

What is the SQL OCTET_LENGTH function?

Returns the number of bytes (octets) in a string or binary expression.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL OCTET_LENGTH: PostgreSQL, MySQL, MariaDB, Amazon Redshift, IBM DB2, Firebird, SAP HANA. Not available in SQL Server or SQLite.

SQL OCTET_LENGTH Full Explanation

OCTET_LENGTH is a scalar string function defined in the SQL standard that calculates the size of a character or binary value in bytes, not characters. It is useful when multi-byte encodings (such as UTF-8 or UTF-16) mean a string’s byte length differs from its character count. If the argument is NULL, the function returns NULL. When applied to fixed-length types (e.g., CHAR(10)), trailing pad bytes are generally included, while for varying types (e.g., VARCHAR) only the stored bytes are counted. Behavior is deterministic and has no side effects.

SQL OCTET_LENGTH Syntax

OCTET_LENGTH ( string_or_binary_expression )

SQL OCTET_LENGTH Parameters

string_or_binary_expression - string, binary, or expression that resolves to those types. Required.

Example Queries Using SQL OCTET_LENGTH

-- Byte length of a simple ASCII string
SELECT OCTET_LENGTH('Galaxy');         -- returns 6

-- Byte length of a multibyte UTF-8 string
SELECT OCTET_LENGTH('✨');             -- returns 3 in UTF-8 databases

-- Check rows exceeding 1 MB in a logs table
SELECT id, OCTET_LENGTH(payload) AS bytes
FROM   app_logs
WHERE  OCTET_LENGTH(payload) > 1048576;

Expected Output Using SQL OCTET_LENGTH

Each query returns an integer representing the number of bytes in the supplied expression
If the expression is NULL the result is NULL

Use Cases with SQL OCTET_LENGTH

Validate payload sizes before inserts or exports.
Compare storage impact of strings across encodings.
Enforce byte-based limits in APIs or messaging queues.
Detect unexpectedly large text or BLOB columns for optimization.

Common Mistakes with SQL OCTET_LENGTH

Confusing OCTET_LENGTH with CHAR_LENGTH; the latter counts characters, not bytes.
Assuming result equals character count in multibyte encodings.
Using on SQL Server, which does not implement the function (use DATALENGTH instead).

First Introduced In

SQL-92 standard

Frequently Asked Questions

What encodings affect OCTET_LENGTH results?

Databases that store text in UTF-8 or UTF-16 will report larger byte counts for characters outside the ASCII range because those characters take multiple bytes.

Does OCTET_LENGTH include trailing spaces in CHAR columns?

Yes. For fixed-length CHAR columns, most databases pad with spaces, and OCTET_LENGTH counts those pad bytes.

Can I use OCTET_LENGTH on BLOB data?

If your database allows passing binary columns to OCTET_LENGTH, it will return the byte size of the BLOB. Otherwise, use a binary-specific size function.

How do I limit row size using OCTET_LENGTH?

Add a CHECK constraint: `CHECK (OCTET_LENGTH(col) <= 1048576)` to reject rows where `col` exceeds 1 MB in storage.