A numeric-text mix-up happens when a database stores a quantity as text, or a label as a number. The values look the same on screen, but sorting, totals and searches behave differently. You are expected to notice the symptom, name the cause and say how to fix it.
This lesson follows importing a small fictional dataset, because imports are where the mix-up usually starts. It belongs to database structure and validation.
What are the symptoms?
Three quick tests reveal how a column is stored.
- Sort test. Sort the column in ascending order. A number field puts 5, 9, 12, 120. A text field puts 12, 120, 5, 9, because it compares character by character.
- Total test. Ask for a sum or average. A text column returns zero, an error or no option at all.
- Zero test. Look at labels such as postcodes. If 08000 is displayed as 8000, a label was stored as a number, and the zero at the start was lost.
Dates have the same trouble. A date written 10-03-2024 stored as text sorts by the first characters, not by the calendar.
Worked example
An invented table of stock quantities was imported. Quantities are 25, 9, 120 and 12.
Step 1, run the sort test. Ascending order shows: 12, 120, 25, 9.
Step 2, compare with the expected order. Treated as numbers, the order should be 9, 12, 25, 120. The sorted result is different, so something is wrong.
Step 3, explain the pattern. Text compares the first character first. The first characters are 1, 1, 2 and 9. Both 12 and 120 start with 1, so they come first. Between those two, 12 is shorter and comes before 120. Then comes 25, which begins with 2, and 9, which begins with 9. The order 12, 120, 25, 9 is exactly what text produces.
Step 4, confirm the cause. The Quantity field type says text.
Step 5, fix it. Change the field type to integer. If the program asks, confirm the conversion. Sort again: 9, 12, 25, 120.
Step 6, check with a total. 25 + 9 + 120 + 12 = 166. Verify by pairs: 25 + 9 = 34 and 120 + 12 = 132, and 34 + 132 = 166. The field can now give the sum.
The mistake to watch for
A frequent error is to “fix” a text column by changing every digit-only field to a number, including phone numbers.
Mistaken fix: The Phone field was converted to integer so that it would sort numerically. A phone value starting with a zero becomes shorter, and the zero is gone.
The value no longer matches the real number. Sorting by phone number is rarely needed, so the conversion solved a problem nobody had and created a real one.
The correction is to ask what the column is for. If the values are added, averaged or compared as amounts, convert. If they identify something, keep text.
When the zero at the start has already been lost, restore the original from the source file by importing again with the field set to text.
Check yourself
1. A text column holds 7, 70 and 8. Write the ascending order the database shows.
Show answer
7, 70, 8. Text compares the first character: 7, 7 and 8. Of the two starting with 7, the shorter value 7 comes first, then 70.
2. A user adds the Price column and the program gives 0 although the prices are 5.50, 3.00 and 2.50. What is the likely cause and the total after fixing it?
Show answer
The prices are stored as text, so they cannot be summed. After converting to currency or decimal, the total is 5.50 + 3.00 + 2.50 = 11.00.
3. A postcode field shows 8000 where the source has 08000. What went wrong, and what is the fix?
Show answer
The postcode was stored as a number, which removed the zero at the start. Set the field to text and re-import from the source.
Where this leads next
Next, see why duplicate rows damage counts and totals in explaining why a duplicate record matters. If field types need a refresh, return to choosing field types from sample values.
Then try the database structure and validation practice set. The mistake log and retest queue is a good place to record each type error you make.
A mix-up like this is easy to see once it has been named and hard to see before. A teacher in online one-to-one ICT tuition can build quick tests with you so you can find the cause on any table.