Command guide

intermediate string-processing MySQL MariaDB SQL Server PostgreSQL SQLite

Pad A String To A Fixed Length With LPAD And RPAD

Left-pad or right-pad a string to a fixed width by filling with a repeated character.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Left-pad numeric product codes to 6 digits

Code 42 is padded with four leading zeros to become 000042. Code 5100 gets two zeros to become 005100. Code 999999 already fills 6 characters so no padding is added. If the source value is longer than the target width, LPAD truncates it in MySQL and MariaDB; PostgreSQL also truncates. The SQL Server RIGHT approach naturally handles this the same way.

MySQL MariaDB

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (id INT, code INT, name VARCHAR(50));

INSERT INTO
  products (id, code, name)
VALUES
  (1, 42, 'Widget'),
  (2, 5100, 'Gadget'),
  (3, 999999, 'Doohickey');

Validated query

SQL

SELECT
  name,
  LPAD (CAST(code AS CHAR), 6, '0') AS padded_code
FROM
  products
ORDER BY
  id;

Expected result

name	padded_code
Widget	000042
Gadget	005100
Doohickey	999999

SQL Server

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (id INT, code INT, name VARCHAR(50));

INSERT INTO
  products (id, code, name)
VALUES
  (1, 42, 'Widget'),
  (2, 5100, 'Gadget'),
  (3, 999999, 'Doohickey');

Validated query

SQL

SELECT
  name,
  RIGHT (REPLICATE ('0', 6) + CAST(code AS VARCHAR), 6) AS padded_code
FROM
  products
ORDER BY
  id;

Expected result

name	padded_code
Widget	000042
Gadget	005100
Doohickey	999999

PostgreSQL

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (id INT, code INT, name VARCHAR(50));

INSERT INTO
  products (id, code, name)
VALUES
  (1, 42, 'Widget'),
  (2, 5100, 'Gadget'),
  (3, 999999, 'Doohickey');

Validated query

SQL

SELECT
  name,
  LPAD (CAST(code AS TEXT), 6, '0') AS padded_code
FROM
  products
ORDER BY
  id;

Expected result

name	padded_code
Widget	000042
Gadget	005100
Doohickey	999999

SQLite

Engine-specific syntax

Source table data

Setup

CREATE TABLE products (id INT, code INT, name VARCHAR(50));

INSERT INTO
  products (id, code, name)
VALUES
  (1, 42, 'Widget'),
  (2, 5100, 'Gadget'),
  (3, 999999, 'Doohickey');

Validated query

SQL

SELECT
  name,
  PRINTF ('%06d', code) AS padded_code
FROM
  products
ORDER BY
  id;

Expected result

name	padded_code
Widget	000042
Gadget	005100
Doohickey	999999

MySQL/MariaDB/PostgreSQL use LPAD. SQL Server uses RIGHT + REPLICATE. SQLite uses PRINTF. All produce identical 6-character zero-padded strings.

Useful when

Where this command helps.

zero-padding numeric codes or identifiers to a fixed display width
aligning columns in fixed-width text output

Explanation

What the command is doing.

LPAD(string, length, pad) extends a string to the given length by prepending the pad character on the left. RPAD does the same on the right. Both functions are available in MySQL, MariaDB, and PostgreSQL with identical syntax. SQL Server has no LPAD or RPAD functions — use RIGHT(REPLICATE(pad, length) + CAST(value AS VARCHAR), length) to left-pad, or LEFT(CAST(value AS VARCHAR) + REPLICATE(pad, length), length) to right-pad. For numeric zero-padding in SQLite, use PRINTF('%0Nd', value) where N is the target width.

Pad A String To A Fixed Length With LPAD And RPAD

Left-pad numeric product codes to 6 digits

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.