Module 3
Data types, locale and nulls
Types, culture-aware conversion of numbers and dates, and the difference between null, blank and error.
You'll be able to
- Set column types deliberately
- Convert dates and numbers with a culture
- Distinguish null, empty text and errors
Lessons
- 1.Choose types deliberately: identifiers are textPick types that preserve meaning, starting with the rule that identifiers are text.
- 2.Convert dates and numbers with a localeUse the source culture so day/month order and decimal separators are interpreted correctly.
- 3.null, empty text and errors are different thingsRecognise and handle null, empty text and error values deliberately.
- 4.Inspect column types and catch conversion errorsVerify a query's column types and find the rows that failed conversion.
Take the data types, locale and nulls quiz (8 questions)
Exercises
- beginner~6 minFree
Keep order numbers' leading zeros (kite shop)
Type a CSV so order numbers stay text (0007, not 7) while quantities and prices become numbers.
- beginner~6 minPro
Keep order numbers' leading zeros (bakery)
Type a CSV so order numbers stay text (0007, not 7) while quantities and prices become numbers.
- beginner~6 minPro
Keep order numbers' leading zeros (bike rental)
Type a CSV so order numbers stay text (0007, not 7) while quantities and prices become numbers.
- intermediate~8 minFree
Convert day-first dates with the right locale (consultancy)
Claim dates are written 05/03/2026 meaning 5 March. Convert them to real dates using the en-GB culture.
- intermediate~8 minPro
Convert day-first dates with the right locale (school)
Claim dates are written 05/03/2026 meaning 5 March. Convert them to real dates using the en-GB culture.
- intermediate~7 minFree
Read amounts written with a decimal comma (consultancy)
A German export writes 1.234,56 for one thousand two hundred thirty-four point five six. Convert it with the de-DE culture.
- intermediate~7 minPro
Read amounts written with a decimal comma (school)
A German export writes 1.234,56 for one thousand two hundred thirty-four point five six. Convert it with the de-DE culture.
- intermediate~7 minFree
Treat empty text and null the same (hardware)
Some suppliers are null and some are empty text. Standardise both to "Unknown".
- intermediate~7 minPro
Treat empty text and null the same (pharmacy)
Some suppliers are null and some are empty text. Standardise both to "Unknown".
- intermediate~7 minPro
Treat empty text and null the same (cafe)
Some suppliers are null and some are empty text. Standardise both to "Unknown".
- intermediate~8 minFree
Predict which orders a filter will drop, nulls included (kite shop)
Before a Qty ≥ 3 filter goes live, list exactly the orders it will remove, including the ones with no quantity.
- intermediate~8 minPro
Predict which orders a filter will drop, nulls included (bakery)
Before a Qty ≥ 3 filter goes live, list exactly the orders it will remove, including the ones with no quantity.
- intermediate~8 minPro
Predict which orders a filter will drop, nulls included (bike rental)
Before a Qty ≥ 3 filter goes live, list exactly the orders it will remove, including the ones with no quantity.
- beginner~7 minFree
Convert Yes/No answers to true, false or null (charity)
The marketing opt-in column mixes Yes, Y, no, N, blanks and nulls. Turn it into a proper true/false column.
- beginner~7 minPro
Convert Yes/No answers to true, false or null (gym)
The marketing opt-in column mixes Yes, Y, no, N, blanks and nulls. Turn it into a proper true/false column.
- intermediate~7 minFree
Split a timestamp into its date and hour (door access)
Each event has a text timestamp like 2026-03-04T14:30:00. Add a real Date and the Hour of day.
- intermediate~7 minPro
Split a timestamp into its date and hour (web orders)
Each event has a text timestamp like 2026-03-04T14:30:00. Add a real Date and the Hour of day.
- beginner~5 minFree
Make a mixed number/text code column all text (garden centre)
Product codes arrive as a mix of numbers (1001) and text (A-17, 0042). Make every code text.
- beginner~5 minPro
Make a mixed number/text code column all text (music shop)
Product codes arrive as a mix of numbers (1001) and text (A-17, 0042). Make every code text.
- beginner~5 minFree
Build a date from year, month and day columns (deliveries)
A legacy export stores dates as three number columns. Combine them into one real date.
- beginner~5 minPro
Build a date from year, month and day columns (inspections)
A legacy export stores dates as three number columns. Combine them into one real date.