FORMAT and LPAD/RPAD in MySQL - Mini Project: Build & Apply

Formatting Numbers and Text with FORMAT, LPAD, and RPAD in MySQL

📖 Scenario: You work in a small retail store's database team. You need to prepare product price and code data so it looks neat and consistent for reports and invoices.

🎯 Goal: Build SQL queries that format prices with commas and decimals, and pad product codes with zeros or spaces to a fixed length.

📋 What You'll Learn

Create a table called products with columns product_id, product_code, and price.

Insert 3 products with specific product_code and price values.

Write a query using FORMAT to display prices with 2 decimal places and commas.

Write queries using LPAD and RPAD to pad product_code to length 8.

💡 Why This Matters

🌍 Real World

Formatting numbers and codes is common in reports, invoices, and user interfaces to improve readability and consistency.

💼 Career

Database developers and analysts often need to format query results for business reports and data exports.

1

Create the products table and insert data

Create a table called products with columns product_id as INT primary key, product_code as VARCHAR(10), and price as DECIMAL(10,2). Then insert these rows exactly: (1, 'A12', 1234.5), (2, 'B7', 98765.432), (3, 'C1234', 56.7).

MySQL

-- Create table and insert data here
-- Your code here

Need a hint?

Use CREATE TABLE with the specified columns and types. Then use INSERT INTO products with the exact rows.

2

Add a query to format prices with commas and 2 decimals

Write a SELECT query that shows product_id and the price formatted with commas and exactly 2 decimal places using the FORMAT function. Name the formatted column formatted_price.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_code VARCHAR(10),
  price DECIMAL(10,2)
);

INSERT INTO products (product_id, product_code, price) VALUES
(1, 'A12', 1234.5),
(2, 'B7', 98765.432),
(3, 'C1234', 56.7);

-- Write SELECT query with FORMAT here
-- Your code here

Need a hint?

Use SELECT product_id, FORMAT(price, 2) AS formatted_price FROM products; to format prices.

3

Use LPAD to pad product_code on the left with zeros

Write a SELECT query that shows product_id and product_code padded on the left with zeros to length 8 using LPAD. Name the padded column padded_code_left.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_code VARCHAR(10),
  price DECIMAL(10,2)
);

INSERT INTO products (product_id, product_code, price) VALUES
(1, 'A12', 1234.5),
(2, 'B7', 98765.432),
(3, 'C1234', 56.7);

SELECT product_id, FORMAT(price, 2) AS formatted_price FROM products;

-- Write SELECT query with LPAD here
-- Your code here

Need a hint?

Use LPAD(product_code, 8, '0') to add zeros on the left until length 8.

4

Use RPAD to pad product_code on the right with spaces

Write a SELECT query that shows product_id and product_code padded on the right with spaces to length 8 using RPAD. Name the padded column padded_code_right.

MySQL

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_code VARCHAR(10),
  price DECIMAL(10,2)
);

INSERT INTO products (product_id, product_code, price) VALUES
(1, 'A12', 1234.5),
(2, 'B7', 98765.432),
(3, 'C1234', 56.7);

SELECT product_id, FORMAT(price, 2) AS formatted_price FROM products;

SELECT product_id, LPAD(product_code, 8, '0') AS padded_code_left FROM products;

-- Write SELECT query with RPAD here
-- Your code here

Need a hint?

Use RPAD(product_code, 8, ' ') to add spaces on the right until length 8.