When a database reads a number field as text, it sorts character by character, so 100 lands before 9, and any test such as “less than 20” gives wrong results. The fix is to clean the values and set the correct field type at the import step, then check a sample against the source.
This page supports database structure and validation and the lesson on detecting an incorrect numeric-text interpretation. The steps are software-neutral, because the wording of menus changes between products and versions.
What is actually going wrong?
A database field has a data type. Text fields store characters and compare them left to right. Number fields store a value and compare it by size. During an import, the software guesses the type from the values it sees, or it uses the type you chose.
There are three usual causes:
- A stray character in one row, such as “RM 2.00”, a trailing space or a comma in “1,200”. One bad value can turn the whole column into text.
- A text default accepted at the import screen. The software offered text for every column and you clicked through.
- A value that should be text but was treated as a number, which is the reverse problem: a phone number loses its leading zero.
A worked example
A fictional canteen file has four rows.
| ItemID | Item | Price | Stock |
|---|---|---|---|
| C01 | Nasi lemak | 3.50 | 12 |
| C02 | Teh ais | RM 2.00 | 9 |
| C03 | Roti canai | 1.50 | 100 |
| C04 | Kuih | 0.80 | 40 |
The student imports it and leaves every column on text. Then they sort Stock ascending and get 100, 12, 40, 9. Text sorting compares the first character first: “100” and “12” both start with 1, and “0” is lower than “2”, so 100 comes before 12. Then 40 and 9 follow because 4 is lower than 9.
The query Stock < 20 returns 100 and 12 as text. It misses 9, because “9” is higher than “2”. The correct numeric answer is 12 and 9.
The fix, step by step:
- Keep the original file and work on a copy.
- Clean the source: change “RM 2.00” to 2.00. Show the RM with a currency format in the database, not inside the value.
- Set types at import: ItemID text, Item text, Price number (decimal), Stock number (whole).
- Check: sort Stock both ways. You should now see 9, 12, 40, 100 ascending.
- Re-run the query: Stock < 20 should return 12 and 9 only.
You can rehearse the sorting and filtering idea with fictional data in the read-only SQL practice lab.
The mistake to watch for
A student changes the Price field to a number after the import and sees the RM 2.00 value go blank. They assume the software is faulty. In fact the value could not be read as a number, so it was dropped or flagged. The correction is to clean the value first, then convert, and always compare records with the original.
Which number-like values should stay text?
| Value | Type | Reason |
|---|---|---|
| Phone number starting with 0 | Text | Leading zero matters; never calculated |
| Postcode 08000 | Text | Leading zero matters |
| Student ID S0042 | Text | Contains a letter and a code pattern |
| Quantity in stock | Number | Sorted and compared by size |
| Price | Number | Added, averaged, compared |
The test is simple: will I add, average or compare it by size? If yes, use a number type.
Check yourself
1. Stock values 5, 40 and 7 are sorted ascending as text. What order appears?
Show answer
Text compares the first character: 4 is lower than 5, and 5 is lower than 7. So the order is 40, 5, 7.
2. A column of ages imports as text because one cell holds “16 yrs”. What are two sensible fixes?
Show answer
Clean the source cell to 16 and set the field type to number before importing, or fix the cell in the database and then convert the type. In both cases check a sample against the original file.
3. Why is a phone number better stored as text?
Show answer
It is an identifier, not a quantity. A number type can remove the initial zero, and you never add or average phone numbers.
Where this leads next
Practise choosing types from sample values with choosing field types, and then importing a small fictional dataset. When you need to show your result as evidence, the ICT practical task and evidence checker gives a software-neutral checklist, and the lesson on capturing evidence that shows the relevant setting covers the screenshot side.
Import problems are easy to fix once you know where to look, and harder when the wrong setting sits on a screen you clicked past. Online one-to-one ICT tuition lets a teacher watch that step with you.