String

🎬 Scenario: Formatting Customer and Product Data

Your manager at Amazon, Sophia, needs cleaner and more structured customer and product reports.

Can you modify the customer names for better readability and extract key details from product names?

Your task is to use string functions like CONCAT() to format customer names and SUBSTRING() to extract meaningful parts of product descriptions. Use the following tables to answer the questions.

amazon_products table:

amazon_orders table:

1. Using CONCAT() to Combine Text

The CONCAT() function joins two or more text values into one.

Syntax

1SELECT CONCAT(string1, string2) AS new_string FROM table_name;

• Joins multiple string columns or values.
• Automatically removes NULL values (returns NULL if any input is NULL).

Example Query: Formatting Customer Full Names

1SELECT customer_id, CONCAT(name, ' (', country, ')') AS formatted_name  
2FROM amazon_customers  
3ORDER BY customer_id;

What’s Happening?

• CONCAT(name, ' (', country, ')') adds the country in parentheses next to each name.
• This makes customer reports more informative.

2. Using SUBSTRING() to Extract Parts of a String

The SUBSTRING() function extracts a portion of a text value.

Syntax

1SELECT SUBSTRING(string_column FROM start_position FOR length) FROM table_name;

• Start position determines where extraction begins (1-based index).
• Length defines how many characters to extract.

Example Query: Extracting Product Categories from Product Names

1SELECT product_id, product_name,  
2       SUBSTRING(product_name FROM 1 FOR 5) AS short_name  
3FROM amazon_products  
4ORDER BY product_id;

What’s Happening?

• The SUBSTRING(product_name FROM 1 FOR 5) extracts the first 5 characters of each product name.
• Useful for creating shorter product codes or abbreviations.

✍️ SQL Exercises

Exercise 1: Formatting Customer Names

Sophia wants a formatted customer name report, showing customer names alongside their country in parentheses.

Filter condition:

• Retrieve customer ID and formatted name (e.g., "Alice Johnson (USA)").
• Sort by customer_id in ascending order.

Write an SQL query to return the requested data.

Exercise 2: Extracting Short Product Names

Sophia also wants to generate product abbreviations by extracting the first five characters of each product name.

Filter condition:

• Retrieve product ID, product name, and a new column short_name containing only the first five characters.
• Sort by product_id in ascending order.

Write an SQL query to return the requested data.