Handling Messy Strings

Overview

Handling Messy Strings — Fixing Real-World Text Problems

Real data is often messy.

People type things differently, add extra spaces, mix uppercase and lowercase, or include symbols that don’t belong.

SQL gives you simple tools to clean this up so your data is consistent and easy to work with.

Messy strings usually involve:

• Extra spaces
• Inconsistent capitalization
• Unwanted characters
• Partial text you need to extract
• Text you need to replace

These functions help beginners fix the most common real-world problems.

---

Common String Cleaning Functions

• TRIM — Remove extra spaces at the start or end
• REPLACE — Swap one piece of text for another
• SUBSTRING — Extract part of a string
• LEFT / RIGHT — Take characters from the left or right
• UPPER / LOWER — Fix inconsistent capitalization

These tools are simple and perfect for beginners.

---

Examples

Example 1: Remove extra spaces from course names

SELECT TRIM(course_name) AS CleanCourseName
FROM courses;
  

Explanation

Fixes things like: ' Intro to Math ' → 'Intro to Math'

---

Example 2: Replace dashes or slashes in department names

SELECT REPLACE(department, '-', ' ') AS CleanDepartment
FROM instructors;
  

Explanation

Turns 'Math-Science' into 'Math Science'.

---

Example 3: Extract the first 3 letters of a department

SELECT department,
       LEFT(department, 3) AS DeptCode
FROM instructors;
  

Explanation

Useful for creating short codes like: MAT, SCI, ENG.

---

Example 4: Extract the last 2 characters of a course ID

SELECT course_id,
       RIGHT(course_id, 2) AS CourseSuffix
FROM courses;
  

Explanation

If course_id is 'CSE101', this returns '01'.

---

Example 5: Replace NULL or blank strings with something meaningful

SELECT COALESCE(
         NULLIF(TRIM(department), ''),
         'Unknown'
       ) AS CleanDepartment
FROM instructors;
  

Explanation

• TRIM removes extra spaces
• NULLIF(..., '') turns empty strings into NULL
• COALESCE replaces NULL with ‘Unknown’

This is a very common pattern for cleaning messy text.

---

Example 6: Standardize capitalization

SELECT UPPER(TRIM(department)) AS CleanDepartment
FROM instructors;
  

Explanation

• Removes spaces
• Converts everything to uppercase
• Makes matching and grouping easier

---

Why String Cleaning Matters

• Real data is rarely perfect
• Extra spaces break joins
• Mixed capitalization causes mismatches
• Strange characters make reports look unprofessional

Beginners quickly learn: clean text = fewer SQL errors.

---

Messy String Tools Summary

Function	Purpose	Beginner Explanation
TRIM	Remove extra spaces	“Clean up the edges.”
REPLACE	Swap text	“Change this into that.”
LEFT / RIGHT	Take characters from one side	“Grab the first or last few letters.”
SUBSTRING	Extract part of text	“Take a piece from the middle.”
UPPER / LOWER	Fix capitalization	“Make everything consistent.”
NULLIF + COALESCE	Fix empty or missing text	“If it’s blank, use something better.”

---

Practice Tasks (Your Turn!)

Task 1

Remove extra spaces from student first and last names.

Task 2

Replace dashes in department names with spaces.

Task 3

Create a 3-letter code for each course name using LEFT.