SQL · Data preparation · Real estate

Nashville Housing Data Cleaning

A walkthrough of my SQL workflow for preparing Nashville housing records: filling missing property addresses, separating address fields, standardizing vacancy labels, and identifying duplicate sales.

SQL ServerT-SQLData CleaningCTEsWindow Functions

View original SQL Repository

From raw records to structured fields

The source comments credit AlecTheAnalyst’s “Nashville Housing Data for Data Cleaning” dataset and describe importing its CSV into Azure Data Studio. My queries work on parcel identifiers, property and owner addresses, vacancy indicators, and sale records. The focus is preparing consistent fields for later reporting—not drawing conclusions about the Nashville housing market.

Environment: SQL Server · Azure Data Studio
Address fields added: 2 property + 3 owner fields
Duplicate review: CTE + ROW_NUMBER

Step 01

Inspect the imported housing records

I imported the Nashville Housing Data for Data Cleaning CSV into a database named “SQL Cleaning” in Azure Data Studio. The opening query reviews the entire table before any changes. This is a data-cleaning workflow in SQL Server’s T-SQL dialect, not an analysis of housing prices.

T-SQL · Original sourceLines 9–11

/*Simply looking at the data*/
select *
from [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]

Step 02

Fill missing property addresses

I ordered the records by ParcelID, then joined the table to itself on the same parcel identifier while excluding the same UniqueID. ISNULL uses the matching record’s address when the original address is NULL. The SELECT previews the matches before the UPDATE writes them back.

T-SQL · Original sourceLines 13–35

/*Populate Property Address Data*/
/* Where column "PropertyAddress" is null, replace with the corresponding duplicate property address*/
/*Eventually we will delete these duplicates */
select *
from [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
--where propertyAddress is null
order by ParcelID

/* Using the ISNULL Function and a self Join to populate 'null' values in the PropertyAddress Column*/
select a.ParcelID, a.PropertyAddress, b.ParcelID, b.PropertyAddress, ISNULL(a.PropertyAddress,b.PropertyAddress)
From [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning] a 
join [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning] b   
    on a.ParcelID =b.ParcelID   
    and a.UniqueID <> b.UniqueID
where a.PropertyAddress is NULL

update a
set PropertyAddress = ISNULL(a.PropertyAddress,b.PropertyAddress)
From [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning] a 
join [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning] b   
    on a.ParcelID =b.ParcelID   
    and a.UniqueID <> b.UniqueID
where a.PropertyAddress is NULL

Reproducibility note: Before rerunning, verify that each parcel has one consistent, non-null address among its matching records. Multiple conflicting matches can make the update ambiguous, and a parcel with no usable match remains missing.

Step 03

Separate property street and city

CHARINDEX locates the comma in PropertyAddress. SUBSTRING extracts the street before it and the city after it. I add PropertySplitAddress and PropertySplitcity as NVARCHAR(255) columns and populate them with UPDATE statements. The preview query labels both expressions “Address”; the stored columns distinguish the two fields.

T-SQL · Original sourceLines 38–61

--breaking out Address into Individual Columns (address, City, State)

select PropertyAddress
from [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
--where propertyAddress is null
--order by ParcelID

/*Using the substring and CharIndex function to separate the property address into separate columns for address and city */
SELECT
substring(propertyAddress,1, CHARINDEX(',',PropertyAddress)-1) as Address,
substring(propertyAddress,CHARINDEX(',',PropertyAddress) +1, len(PropertyAddress)) as Address
from [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]

Alter Table [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
add PropertySplitAddress Nvarchar(255);

Update [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
set PropertySplitAddress = substring(propertyAddress,1, CHARINDEX(',',PropertyAddress)-1)

Alter Table [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
add PropertySplitcity Nvarchar(255);

Update [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
set PropertySplitcity = substring(propertyAddress,CHARINDEX(',',PropertyAddress) +1, len(PropertyAddress))

Reproducibility note: This parsing assumes a comma is present. Missing delimiters can cause an invalid substring length, and leading spaces are not trimmed by the original code.

Step 04

Separate owner street, city, and state

For OwnerAddress, I replace commas with periods and use PARSENAME to extract the three parts from right to left: street (3), city (2), and state (1). Three ALTER TABLE / UPDATE pairs create and populate OwnerSplitAddress, OwnerSplitCity, and OwnerSplitState.

T-SQL · Original sourceLines 65–89

/* Using an easier method, i.e the parsename function to separate OwnerAddress into address, city, state columns*/

select 
PARSENAME(Replace(OwnerAddress, ',','.') , 3)
,PARSENAME(Replace(OwnerAddress, ',','.') , 2)
,PARSENAME(Replace(OwnerAddress, ',','.') , 1)
from [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]

Alter Table [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
add OwnerSplitAddress Nvarchar(255);

Update [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
set OwnerSplitAddress = PARSENAME(Replace(OwnerAddress, ',','.') , 3)

Alter Table [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
add OwnerSplitCity Nvarchar(255);

Update [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
set OwnerSplitCity = PARSENAME(Replace(OwnerAddress, ',','.') , 2)

Alter Table [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
add OwnerSplitState Nvarchar(255);

Update [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
set OwnerSplitState = PARSENAME(Replace(OwnerAddress, ',','.') , 1)

Reproducibility note: PARSENAME is an identifier parser, not a general address parser. This approach assumes three comma-separated parts and no embedded periods; malformed or overlong parts can return NULL. Validate the split fields against the originals before dropping any source columns.

Step 05

Standardize the vacancy labels

A grouped COUNT profiles the values in SoldAsVacant. A CASE expression previews the conversion of “Y” to “Yes” and “N” to “No”, leaving every other value unchanged. The UPDATE applies that same expression so abbreviated and full-word labels can be grouped consistently.

T-SQL · Original sourceLines 92–113

/*Change Y and N to Yes and No in "Sold As Vacant" Column*/

/*Using the count function to see how many rows use Y, N, Yes, No */
select Distinct(SoldAsVacant), COUNT(SoldAsVacant)
from [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
group by SoldAsVacant
order by 2

/*Using the Case function to change 'Y' and 'N' rows to 'Yes' and 'No' */
select SoldASVacant
, CASE When SoldAsVacant = 'Y' Then 'Yes'
        When SoldAsVacant = 'N' Then 'No'
        Else SoldAsVacant
        END
        from [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]


Update [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
set SoldAsVacant = CASE When SoldAsVacant = 'Y' Then 'Yes'
        When SoldAsVacant = 'N' Then 'No'
        Else SoldAsVacant
        END

Reproducibility note: COUNT(SoldAsVacant) counts non-null values. A NULL group would therefore show zero, rather than the number of rows in that group.

Step 06

Identify duplicate sale records

The RowNumCTE common table expression assigns ROW_NUMBER within groups sharing ParcelID, PropertyAddress, SalePrice, SaleDate, and LegalReference. Ordering by UniqueID places one record first; row_num > 1 returns the additional records for review. A CTE is a statement-scoped query expression, not a stored temporary table.

T-SQL · Original sourceLines 116–134

    /* Remove Duplicates using a CTE, aka temporary table*/
   With RowNumCTE As(
    SELECT *, 
        ROW_NUMBER() over (
            Partition by ParcelID,
                         PropertyAddress,
                         SalePrice,
                         SaleDate,
                         LegalReference
                         Order by 
                         UniqueID ) row_num
from [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
--order by ParcelID
)
--Delete
Select *
From RowNumCTE
where row_num > 1
--order by PropertyAddress

Reproducibility note: The DELETE line is commented out. The original script selects duplicate candidates; it does not remove them. No duplicate count or deletion result is supplied in the file.

Step 07

Drop redundant source columns

The final ALTER TABLE drops OwnerAddress, TaxDistrict, and PropertyAddress. The newly created address fields retain the intended street, city, and state components. This is the final schema-cleanup statement in the source.

T-SQL · Original sourceLines 137–140

/* Delete Unused Columns*/

ALTER Table [SQL Cleaning ].dbo.[Nashville Housing Data for Data Cleaning]
Drop Column OwnerAddress, TaxDistrict, PropertyAddress

Reproducibility note: This step is destructive. Back up the table and validate the split fields before running it. The duplicate-review query depends on PropertyAddress, so it must run before that column is dropped. Adding columns and dropping source fields also means the script cannot be rerun unchanged.

Workflow summary

What the script prepares

Uses shared parcel identifiers to backfill missing property addresses where a matching address is available.
Creates five separate address fields and normalizes Y/N vacancy labels to Yes/No.
Flags duplicate candidates using a five-field sale key. The original code does not execute a duplicate deletion.
Ends by dropping three source columns after the address-splitting steps.

Source and reproducibility

All query blocks above preserve the original SQL. The linked file provides no result tables, before-and-after row counts, or execution logs, so no cleaning totals or outputs are invented here. To rerun the workflow, import the original CSV into a SQL Server database, adapt the database and table names, and work on a backed-up copy. Terminate the statement before WITH with a semicolon when executing the CTE in the same batch. Review join matches, address parsing, and duplicate candidates before committing changes or dropping source fields.

Explore the original code All projects