Cleaning Data in SQL Server Databases

Start Free Preview

Buying via our links supports this free directory · Affiliate disclosure.
Course facts
  • Payment: Free Preview (Paid Upgrade)
  • Learning style: Self-paced, start anytime
  • Format: Text & Interactive
  • Video access: Streaming Only
  • Before you start: Intermediate SQL Server
DataCamp details
  • Chapters: 4
Created byDataCamp
Available onDataCamp

Course Details
Learning Style

Start Anytime

Level

Intermediate

Study Mode

100% Online

Duration

Short (< 10 Hours)

Languages
Audio Language

English

Outcomes
Certificate

Certificate of Completion

Price & Terms
Pricing Model

Free Preview (Paid Upgrade)

Technical
Content Medium

Text & Interactive

Video Access

Streaming Only

Specification
Access

Subscription

SKU: cleaning-data-sql-server-databases Category: Tags: , , Brand:

Clean messy data in SQL Server: trim and compare strings, handle missing values and duplicates, convert types, match patterns and pivot rows and columns.

At a glance
Duration4 hrs
LevelIntermediate
CertificateCertificate of Completion
LanguageEnglish
Taught byMiriam Antona
AccessSubscription
Add your review
Add to wishlistAdded to wishlistRemoved from wishlist 0
Add to compare

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.


Our take

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.

User Reviews

0.0 out of 5
★★★★★
0
★★★★★
0
★★★★★
0
★★★★★
0
★★★★★
0

No reviews yet. Be the first to review this course.

There are no reviews yet.

Write your review

Reviews can only be posted by registered learners. Log in, then you will return here to post.

×

    Report this page
    Cleaning Data in SQL Server Databases
    Cleaning Data in SQL Server Databases
    LiveTalent
    Logo
    Compare items
    • Total (0)
    Compare
    0