How to convert a string to a numeric value in SQL

Problem

How can you convert a string to a numeric value in SQL?

Sample Data

Consider the following product table in your SQL database:

CREATE TABLE product (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price VARCHAR(100)
);

INSERT INTO product (id, name, price)
VALUES
  (1, 'Product 1', '100.50'),
  (2, 'Product 2', '200.75'),
  (3, 'Product 3', '150.30');

Here, the price is stored as a string. We need to convert it into a numeric value.

MySQL Solution

SELECT id, name, CAST(price AS DECIMAL(10,2)) AS price
FROM product;

PostgreSQL Solution

SELECT id, name, CAST(price AS NUMERIC) AS price
FROM product;

Explanation

In SQL, the CAST function is used to convert a column's data type to another data type.

MySQL Explanation

In the MySQL solution, the CAST function is used with the DECIMAL(10,2) data type to convert the price column from a string to a decimal number. The 10 in DECIMAL(10,2) indicates the total number of digits that the decimal number will have, and the 2 indicates the number of digits to the right of the decimal point.

PostgreSQL Explanation

In the PostgreSQL solution, the CAST function is used with the NUMERIC data type to convert the price column from a string to a numeric value. The NUMERIC data type is used in PostgreSQL to store numeric data. It can store numbers with a lot of digits and perform calculations exactly. It is especially useful when precision is required.