SQL PAD

What is the PAD keyword in SQL collations?

PAD (in PAD SPACE or NO PAD) sets how trailing spaces are handled in character string comparisons within a collation.

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 PAD: MySQL 8.0+, MariaDB 10.10+, Firebird 3.0+, IBM Db2 LUW. Not supported in PostgreSQL, SQL Server, Oracle, or SQLite.

SQL PAD Full Explanation

PAD is a reserved keyword defined by the SQL Standard and appears in two phrases: PAD SPACE and NO PAD. The keyword is used inside CREATE COLLATION and ALTER COLLATION statements to control whether trailing space characters are considered significant when two character strings are compared.PAD SPACE - the default in most systems - means strings are right-padded with spaces to equal length before comparison. Therefore 'abc' and 'abc ' are treated as equal.NO PAD disables this behavior. Comparisons are performed byte-for-byte without adding trailing spaces, so 'abc' is less than 'abc '.The choice affects ORDER BY, DISTINCT, GROUP BY, UNIQUE constraints, hash joins, index builds, and any operation that relies on equality or ordering. Changing the pad attribute on an existing collation requires rebuilding indexes that use that collation. Not all database engines expose PAD/NO PAD even though the SQL Standard defines it; where unsupported the statement will fail with a syntax error.

SQL PAD Syntax

CREATE COLLATION collation_name
    FROM existing_collation
    PAD SPACE;

CREATE COLLATION collation_name
    FROM existing_collation
    NO PAD;

SQL PAD Parameters

Example Queries Using SQL PAD

-- Create a collation that ignores trailing spaces
CREATE COLLATION nls_pad_space
    FROM utf8mb4_0900_ai_ci
    PAD SPACE;

-- Create a collation that treats trailing spaces as significant
CREATE COLLATION nls_no_pad
    FROM utf8mb4_0900_ai_ci
    NO PAD;

-- Use the collation in a table
CREATE TABLE customers (
    name VARCHAR(50) COLLATE nls_no_pad PRIMARY KEY
);

-- Comparison test
SELECT 'abc' = 'abc   ' COLLATE nls_pad_space  AS pad_cmp,   -- returns 1 (true)
       'abc' = 'abc   ' COLLATE nls_no_pad    AS nopad_cmp; -- returns 0 (false)

Expected Output Using SQL PAD

The CREATE COLLATION statements register two new collations
Subsequent comparisons using the PAD SPACE collation evaluate 'abc' and 'abc ' as equal, while the NO PAD collation treats the strings as different

Use Cases with SQL PAD

Enforce strict text comparisons where trailing spaces must be preserved (NO PAD)
Maintain legacy behavior that ignores trailing blanks in fixed-width imports (PAD SPACE)
Guarantee deterministic ordering in indexes built on VARCHAR columns
Prevent subtle uniqueness violations when applications insert padded values

Common Mistakes with SQL PAD

Thinking PAD is a string function like LPAD/RPAD
Omitting the SPACE keyword after PAD (the SQL Standard requires PAD SPACE, not just PAD)
Assuming all engines support PAD/NO PAD; many throw a syntax error (e.g., PostgreSQL, SQL Server, SQLite)
Forgetting to rebuild indexes after changing a collation's pad attribute

First Introduced In

ISO/IEC 9075:1999 (SQL-99)

Frequently Asked Questions

What is the difference between PAD SPACE and NO PAD?

PAD SPACE pads strings with spaces to equal length before comparison, while NO PAD compares the raw byte sequences and treats trailing blanks as significant.

Is PAD a function I can call in a SELECT statement?

No. PAD is a reserved keyword used only inside CREATE COLLATION or ALTER COLLATION statements. Use LPAD/RPAD functions if you need to pad a string.

Why does my database give a syntax error when I use PAD SPACE?

Not every engine implements the SQL Standard's PAD attribute. PostgreSQL, SQL Server, Oracle, and SQLite currently do not support it, so those systems raise a syntax error.

Do I need to rebuild indexes after changing PAD settings?

Yes. If you alter a collation from PAD SPACE to NO PAD or vice versa, any index, unique constraint, or hash that relies on the collation must be rebuilt to ensure correct ordering and uniqueness guarantees.