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

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

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

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

Hint

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

Practice

1. What does the MySQL function FORMAT(12345.6789, 2) do?

easy

A. Formats the number with commas and 2 decimal places, like '12,345.68'.

B. Rounds the number to the nearest integer, like '12346'.

C. Converts the number to a string without decimals, like '12345'.

D. Adds leading zeros to make the number 2 digits long.

Solution

1. Step 1: Understand FORMAT function usage

   The FORMAT function formats a number with commas and rounds it to the specified decimal places.

2. Step 2: Apply FORMAT(12345.6789, 2)

   The number 12345.6789 is rounded to 2 decimals and commas are added, resulting in '12,345.68'.

3. Final Answer:

   Formats the number with commas and 2 decimal places, like '12,345.68'. -> Option A

4. Quick Check:

   FORMAT adds commas and decimals = A [OK]

Hint: FORMAT adds commas and rounds decimals [OK]

Common Mistakes:

• Thinking FORMAT rounds to integer only
• Confusing FORMAT with LPAD or RPAD
• Expecting no commas in output

2. Which of the following is the correct syntax to add leading zeros to make a string 6 characters long using LPAD in MySQL?

easy

A. LPAD(123, '6', '0')

B. LPAD('123', '6', 0)

C. LPAD('123', 6)

D. LPAD('123', 6, '0')

Solution

1. Step 1: Recall LPAD syntax

   LPAD(string, length, pad_string) pads the string on the left to the given length using pad_string.

2. Step 2: Check each option

   LPAD('123', 6, '0') uses correct types: string '123', length 6, pad '0'. Others have wrong types or missing arguments.

3. Final Answer:

   LPAD('123', 6, '0') -> Option D

4. Quick Check:

   LPAD needs string, length, pad string = C [OK]

Hint: LPAD needs string, length, and pad string in order [OK]

Common Mistakes:

• Using numbers instead of strings for pad character
• Missing the pad string argument
• Passing length as a string instead of number

3. What is the result of this query?
SELECT LPAD('abc', 6, '*'), RPAD('abc', 6, '!');

medium

A. 'abc***', '!!!abc'

B. '***abc', 'abc!!!'

C. 'abc', 'abc'

D. '***abc', '!!!abc'

Solution

1. Step 1: Understand LPAD and RPAD behavior

   LPAD pads on the left, RPAD pads on the right to reach total length 6.

2. Step 2: Calculate LPAD('abc', 6, '*')

   'abc' length is 3, so 3 '*' are added on left: '***abc'.

3. Step 3: Calculate RPAD('abc', 6, '!')

   Similarly, 3 '!' are added on right: 'abc!!!'.

4. Final Answer:

   '***abc', 'abc!!!' -> Option B

5. Quick Check:

   LPAD left stars, RPAD right exclamations = B [OK]

Hint: LPAD pads left, RPAD pads right to total length [OK]

Common Mistakes:

• Confusing LPAD and RPAD directions
• Not counting original string length
• Expecting padding to replace characters

4. Identify the error in this query:
SELECT LPAD('test', 4, '0');

medium

A. Syntax error due to missing RPAD.

B. Error because length is less than string length.

C. No error; it returns 'test'.

D. Error because pad string must be numeric.

Solution

1. Step 1: Check LPAD length vs string length

   LPAD pads string to length 4, but 'test' length is 4, so no padding needed.

2. Step 2: Confirm behavior when length equals string length

   LPAD returns original string if length equals string length, no error occurs.

3. Final Answer:

   No error; it returns 'test'. -> Option C

4. Quick Check:

   LPAD returns original if length equals string length = D [OK]

Hint: LPAD returns original if length equals string length [OK]

Common Mistakes:

• Expecting error when length equals string length
• Thinking pad string must be numeric
• Confusing LPAD with RPAD requirements

5. You want to display a price column with 2 decimals and commas, and pad the result on the left with '#' to make it 12 characters wide. Which query achieves this?

hard

A. SELECT LPAD(FORMAT(price, 2), 12, '#') FROM products;

B. SELECT FORMAT(LPAD(price, 12, '#'), 2) FROM products;

C. SELECT RPAD(FORMAT(price, 2), 12, '#') FROM products;

D. SELECT LPAD(price, 12, '#') FROM products;

Solution

1. Step 1: Format price with commas and 2 decimals

   Use FORMAT(price, 2) to get string like '1,234.56'.

2. Step 2: Pad formatted string on left to length 12 with '#'

   Use LPAD(formatted_string, 12, '#') to add '#' on left if needed.

3. Step 3: Check options

   SELECT LPAD(FORMAT(price, 2), 12, '#') FROM products; correctly formats then pads left. SELECT FORMAT(LPAD(price, 12, '#'), 2) FROM products; tries to pad number before formatting (wrong). SELECT RPAD(FORMAT(price, 2), 12, '#') FROM products; pads right, not left. SELECT LPAD(price, 12, '#') FROM products; pads number without formatting.

4. Final Answer:

   SELECT LPAD(FORMAT(price, 2), 12, '#') FROM products; -> Option A

5. Quick Check:

   Format first, then LPAD left padding = A [OK]

Hint: Format number first, then LPAD to pad left [OK]

Common Mistakes:

• Padding before formatting number
• Using RPAD instead of LPAD for left padding
• Padding raw number without formatting