Cleaning data is a must for accurate analysis and efficient reporting. Messy data leads to errors, slow queries, and unreliable insights. Luckily, SQL has powerful functions like CAST(), CONVERT(), and COALESCE() to fix formatting issues, handle NULLs, and standardize data types. Learning these has been a game changer for me. It saves so much time compared to manual fixes in Excel. The best part? You can save your SQL queries and reuse them anytime.

1. CAST() – Changing Data Types Safely

The CAST() function converts one data type into another, making it easier to work with inconsistent data across tables or sources. It also helps clean up your output, like turning datetime values into just dates.

● Example: Converting Strings to Numbers

Sometimes, numeric values are stored as text, which can cause issues in calculations. You can use CAST() to convert them into numbers:

SELECT CAST('123.45' AS DECIMAL(10,2)) AS ConvertedValue;

Output:

ConvertedValue
123.45

● Example: Converting Dates

If dates are stored as strings, converting them to a proper DATE type allows for accurate filtering and calculations:

SELECT CAST('2025-03-21' AS DATE) AS FormattedDate;

Output:

FormattedDate
2025-03-21

2. CONVERT() – Formatting Data for Readability

While CAST() only changes data types, CONVERT() (specific to SQL Server) provides more control over formatting, especially for dates and numeric values.

● Example: Formatting Dates

You can format dates using CONVERT() to display them in a preferred style.

SELECT CONVERT(VARCHAR, GETDATE(), 101) AS US_Date_Format;

Output:

US_Date_Format
03/09/2025

Here, 101 represents the MM/DD/YYYY format. Other common date formats include:

103 → DD/MM/YYYY

104 → DD.MM.YYYY

120 → YYYY-MM-DD (ISO standard)

● Example: Converting Numbers to Strings

SELECT CONVERT(VARCHAR, 12345) AS StringNumber;

Output:

StringNumber
12345

Tip: Use CONVERT() when you need more formatting options, especially for date and numeric conversions in SQL Server.


3. COALESCE() – Handling NULL Values

NULL values can mess up calculations and reports, but COALESCE() fixes that by replacing them with a default value. No more crashing Excel trying to swap nulls for zeros in massive spreadsheets!

● Example: Replacing NULLs with a Default Value

SELECT COALESCE(ProductWeight, 'No Data') AS CleanedWeight;

Output:

CleanedWeight
No Data

● Example: Handling NULLs in Calculations

If a table has missing sales data, COALESCE() ensures calculations don’t break:

SELECT ProductName, COALESCE(SalesAmount, 0) AS SalesAmount
FROM Sales;

If SalesAmount is NULL, it will be replaced with 0, preventing issues and providing a cleaner output.

Final Thoughts

By applying these functions, you can ensure your data is clean, accurate, and ready for analysis:
» CAST() → Converts data types for consistency.
» CONVERT() → Changes data types with formatting options.
» COALESCE() → Replaces NULL values with meaningful defaults.

I cannot emphasize enough the time saved on data cleaning by incorporating these functions. Instead of exporting results to a spreadsheet to fix formats and nulls, you can handle everything within your query and reuse it later. Try out these functions in your next SQL project to improve data quality and streamline your workflow.