Think of it as a pipeline:

Raw Data → TRIM() → LOWER()/UPPER() → REPLACE() → Clean Data

⚠️ 23. Common Mistakes

Mistake 1 — Ignoring spaces:

• 'Alice' and ' Alice' may behave as different values depending on the database and comparison context.

• Use TRIM(customer_name) when appropriate.

Mistake 2 — Ignoring capitalization:

• Premium, premium, PREMIUM can create inconsistent groups.

• Use UPPER(TRIM(customer_type)) when the business meaning is case-insensitive.

Mistake 3 — Assuming all databases use the same syntax:

• String functions differ between PostgreSQL, MySQL, SQL Server, and Oracle.

• Always verify the syntax for your SQL dialect.

Mistake 4 — Modifying data unnecessarily:

• There is a difference between SELECT TRIM(name) and actually updating the stored value.

• Always understand whether you're transforming data for analysis or permanently modifying the database.

🎤 SQL Interview Questions

Q1. What is the purpose of string functions?

• They are used to manipulate, clean, transform, search, and extract text data.

Q2. What does TRIM() do?

• It removes leading and trailing spaces from a string.

Q3. Difference between UPPER() and LOWER()?

• UPPER() converts text to uppercase. LOWER() converts text to lowercase.

Q4. What does CONCAT() do?

• It combines multiple strings into one value.

Q5. What does REPLACE() do?

• It replaces occurrences of one substring with another.

Q6. How can you find the length of a string?

• Commonly LENGTH(column_name) or, depending on the database, CHAR_LENGTH(column_name).

Q7. How would you standardize customer categories?

• For example UPPER(TRIM(customer_type)). This removes surrounding spaces and standardizes capitalization.

Q8. How can you extract the last four characters of a value?

• In databases supporting it: RIGHT(column_name, 4).

Q9. How can you combine first and last names?

• CONCAT(first_name, ' ', last_name)

Q10. Why are string functions important for data analysts?

• Because real-world text data often contains inconsistent capitalization, spaces, formats, prefixes, suffixes, and unwanted characters.

📝 Practice Questions

Practice 1: Convert customer names to uppercase.

SELECT UPPER(customer_name) AS customer_name FROM customers;

Practice 2: Remove unnecessary spaces from product names.

SELECT TRIM(product_name) AS product_name FROM products;

Practice 3: Create a full name from first and last name.

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM customers;

Practice 4: Remove hyphens from phone numbers.

SELECT REPLACE(phone, '-', '') AS cleaned_phone FROM customers;

Practice 5: Find products whose names contain more than 50 characters.

SELECT * FROM products WHERE LENGTH(product_name) > 50;

🧪 Mini SQL Challenge

You have this table:

customers: customer_id, first_name, last_name, email, customer_type, phone

Write a query that returns: Customer ID, Cleaned full name, Cleaned lowercase email, Standardized customer type, Phone number without hyphens.

Solution:

SELECT
    customer_id,
    CONCAT(TRIM(first_name), ' ', TRIM(last_name)) AS full_name,
    LOWER(TRIM(email)) AS cleaned_email,
    UPPER(TRIM(customer_type)) AS customer_type,
    REPLACE(TRIM(phone), '-', '') AS cleaned_phone
FROM customers;

This single query demonstrates a practical data-cleaning workflow using several string functions.

📌 Double Tap ❤️ For More