---
title: "Data types and casting with CAST | Oplema"
description: "Learn the common data types, implicit vs explicit conversion, CAST in SQL, how to keep leading zeros and when to parse a value instead of casting it."
url: "https://oplema.com/learn/data-preparation/types-and-casts"
updated: "2026-11-04"
---

Lesson 4 of 15 · Lab

# Data types and casting: CAST vs parsing

A column's type says what its values are: text, a number, a date or true/false. Casting converts a column to another type so you can calculate with it. It can succeed on every row and still be wrong, so check a few values you know.

This lesson's data comes from [fictional Vandelay Coffee Supply](https://oplema.com/learn/data-preparation/what-is-data-prep), which writes amounts the European way, so `1.240` means €1,240. Its 18 stacked invoice rows still hold `amount` as text.

This flow casts `amount` to DOUBLE, the usual type for numbers with decimals, and adds it up. Does it reach the €15,850 in Vandelay's sales report?

Not close. DOUBLE reads the dot as a decimal point, so €1,240 becomes 1.24. Nothing fails, because 1.24 is a valid number.

## Replace a cast with a parse

1. **Summarize** opens first in this live flow in [Strata](https://oplema.com/strata), Oplema's browser app for data prep. `total` reads 5,889.97, and nothing is red.
2. Select the **Select** block, just before **Summarize**. INV-1002 shows `amount_eur` as 1.24.
3. Press Delete. The flow stays red until you reconnect it.
4. In the palette, open **Parse** and drag **Number** into the gap.
5. Drag **Union**'s right dot to **Number**'s left dot, then **Number**'s right dot to **Summarize**'s left dot. Drop on the dot. New to wiring blocks? [Build your first data flow](https://oplema.com/learn/export-to-report/first-data-flow) shows how.
6. Select **Number**. Set **Column** to `amount`, **Text looks like** to the EU option, and **Output column** to `amount_eur`.
7. Select **Summarize** and read `total` again.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

The total you should see

`total` reads 15,850, the sales report's figure, and INV-1002 is 1,240 in **Number**'s preview. `rows` says 18 and `priced` says 16, because `void` and `TBC` became null.

## A parse makes you state the format

That's the sales report's number exactly, so whatever is wrong with that report, it isn't arithmetic.

A cast assumes the text is already in the form the type expects. A [parse](https://oplema.com/docs/strata/parse/number) makes you state the format, so the file's convention becomes a decision you can show someone. Use a parse whenever a number was written for people. Leave the two null rows null, because an unknown price is not a price of zero.

## Common types, and when conversion happens

Types you'll meet most
| Type | Holds | Example |
| --- | --- | --- |
| Text (VARCHAR) | Any characters | `C2`, `00101` |
| Integer | Whole numbers | 18 |
| Decimal or DOUBLE | Numbers with fractions | 1.24 |
| Date | Calendar days | 2026-07-02 |
| Boolean | True or false | true |

Conversion is explicit when you ask for it and implicit when the software decides. Excel's `="5"+3` gives 8 with no request from you. Explicit is safer because it's written down.

A code made of digits is still text. Convert the product code `00101` to a number and it becomes 101, and it no longer matches anything. Don't convert identifiers, and never add them up.

## CAST and VALUE

In DuckDB, write `CAST(amount AS DOUBLE)`, or DOUBLE PRECISION in PostgreSQL and FLOAT in SQL Server. Most databases stop with an error on `TBC`, and `TRY_CAST`, where it exists, returns null instead. For `1.240`, remove the separator first with `CAST(REPLACE(amount, '.', '') AS DOUBLE)`.

Excel's `VALUE` follows your computer's regional settings, so one file can read differently on two laptops. `=NUMBERVALUE(A2, ",", ".")` names the decimal and thousands separators. To keep leading zeros, import the column as text. Opening a CSV directly strips them.
