How to Convert a String to a Numeric Value in SQL

Converting a String to a Numeric Value in SQL

Have you ever needed to convert a string to a numeric value in SQL? Whether you're dealing with a column of text data that needs to be converted to a number, or you're trying to perform calculations on a string-based value, this can be a tricky task. Fortunately, SQL provides a few different ways to convert strings to numeric values.

The Solution

The most common way to convert a string to a numeric value is to use the CAST() function. This function takes two arguments: the string to be converted, and the data type to convert it to. For example, if you wanted to convert the string '123' to an integer, you would use the following query:

SELECT CAST('123' AS INTEGER);

This query would return the result 123.

Examples

Suppose you have a table called users with an age column stored as text. If you wanted to convert the age column to a numeric value, you would use the following query:

SELECT CAST(age AS INTEGER) FROM users;

This query would return the ages as proper integers you can now sum, average, or filter on.

Additional Info

The CAST() function is supported by most major databases, including MySQL, PostgreSQL, and SQL Server. However, the syntax may vary slightly depending on the database you are using. Watch out for locale differences — a comma decimal separator in a French export will silently truncate in an en-US session.