Cleaning Data in SQL Server Databases
Clean messy data in SQL Server: trim and compare strings, handle missing values and duplicates, convert types, match patterns and pivot rows and columns.
Queries that tidy messy data inside a SQL Server database are the focus here. You trim stray spaces from text, bring inconsistent flight registration formats into line, spot names that sound alike, and decide what to do with gaps and repeated rows. Later work handles values outside a sensible range, mismatched data types, pattern checks and reshaping columns and rows. Practice runs on monthly airline flights by airport, TV series records and paper shop sales.
What You Will Achieve
- Spot near-identical names with SOUNDEX() and DIFFERENCE() and bring inconsistent text entries into a single format
- Choose between dropping empty values and filling them with ISNULL() or COALESCE(), and find and treat duplicate rows
- Catch values that fall outside an expected range and test fields such as URLs and phone numbers against a pattern
- Merge several columns into one, split one column into several, and turn rows into columns with PIVOT or back with UNPIVOT
Program Overview
Cleaning Messy Strings and Comparing Names
Why cleaning matters, then the first fixes for text fields in SQL Server, including measuring how similar two strings are.
- Leading and trailing spaces
- Unifying flight registration formats
- Combining strings
- String similarity with SOUNDEX() and DIFFERENCE()
Missing, Duplicate, and Inaccurate Data
Handling gaps, repeats, date formats, out-of-range values, type conversion and pattern matching to find outliers.
- Removing or filling missing values with ISNULL() and COALESCE()
- Diagnosing and treating duplicates
- Date formats with CONVERT() and FORMAT()
- Out-of-range values, CAST() conversions and pattern matching
Combining, Splitting, and Transforming Data
Reshaping data by joining columns, breaking one column apart and switching rows and columns.
- Concatenating cities and states and DATEFROMPARTS()
- SUBSTRING(), CHARINDEX(), RIGHT(), LEFT() and REVERSE()
- PIVOT and UNPIVOT
Certificate Awarded (Paid)
Job Outlook
Cleaning queries like these support data analysis, data preparation and data engineering work wherever the source data is held in SQL Server databases.
Good for: Analysts who keep data in SQL Server and want a set of query patterns for fixing text, gaps, duplicates and values out of range.
Keep in mind: The functions and syntax are specific to SQL Server; the cleaning ideas carry over to other SQL dialects, but the queries need adjusting there.
Bottom line: A function-by-function workout in SQL Server data cleaning, practiced on messy airline, TV series and paper shop data.
Frequently Asked Questions
How does the course split one column into several?
The final chapter splits data of one column into more columns with SUBSTRING() and CHARINDEX(), then with RIGHT(), LEFT() and REVERSE(), and a lesson weighs when to use SUBSTRING() or CHARINDEX().
What messy data do the exercises clean?
The exercises use monthly airline flights by airport, TV series data, including a task to find the series with the most episodes, and paper shop sales records.
Which SQL Server functions handle dates in this course?
CONVERT() and FORMAT() are used to deal with different date formats, with a lesson comparing the two. DATEFROMPARTS() appears later, in the chapter on combining data from several columns.
What does the pattern matching work check?
Pattern matching is used to find outliers in the data. The lessons cover the characters that specify a pattern, then apply them to matching URLs and checking phone numbers.


There are no reviews yet.