CAST() and type conversion

CAST() converts a value from one data type to another - turning text that looks like a number into a real number SQL can actually do math on.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Converting Retail Text Values with CAST()

SELECT order_id,
       CAST(revenue AS NUMERIC) AS revenue_numeric,
       CAST(quantity AS INTEGER) AS quantity_int,
       CAST(order_date AS DATE) AS order_date_typed
FROM orders

Result:

order_id	revenue_numeric	quantity_int	order_date_typed
O2001	149.99	3	2024-03-01
O2002	89.5	1	2024-03-05
O2003	320.0	5	2024-03-10
O2004	45.75	2	2024-03-12
O2005	210.3	4	2024-03-18

Step by step

Run the query without CAST so you can see what the unfiltered values look like.

SELECT order_id,
       revenue,
       CAST(quantity AS INTEGER) AS quantity_int,
       CAST(order_date AS DATE) AS order_date_typed
FROM orders;

CAST changes a value from one data type to another.

SELECT order_id,
       CAST(revenue AS NUMERIC),
       CAST(quantity AS INTEGER) AS quantity_int,
       CAST(order_date AS DATE) AS order_date_typed
FROM orders;

AS gives the CAST result a friendly column name so the output reads cleanly.

SELECT order_id,
       CAST(revenue AS NUMERIC) AS revenue_numeric,
       CAST(quantity AS INTEGER) AS quantity_int,
       CAST(order_date AS DATE) AS order_date_typed
FROM orders;

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query to convert the revenue column to text.

SELECT CAST(revenue AS TEXT) FROM orders

2. Using store_sales, write a query to convert the revenue column to text.

SELECT CAST(revenue AS TEXT) FROM store_sales

3. Using inventory, write a query to convert the stock_count column to text.

SELECT CAST(stock_count AS TEXT) FROM inventory

4. Using shipments, write a query to convert the weight_kg column to text.

SELECT CAST(weight_kg AS TEXT) FROM shipments

5. Using promotions, write a query to convert the discount_pct column to text.

SELECT CAST(discount_pct AS TEXT) FROM promotions

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed