SQL Convert Varchar to Numeric

Conversion is a prevalent task for programmers and database engineers. This article will explore how to convert a varchar type to a numeric type in Standard SQL.

It must be taken into consideration that when we say Standard SQL, we mean techniques and methods universally adopted by relational database engines.

SQL Cast Method

In Standard SQL, typecasting is accomplished by using the CAST() function. The function syntax is as shown:

CAST ( expression AS t_type [ ( LENGTH ) ] );

The function takes three main parameters:

The expression refers to any value that can be converted to a specified type.
T type refers to the data type to which the specified value should be converted: The target type can include any supported Standard SQL type such as INT64, NUMERIC, BIGNUMERIC, DATE, STRING, BYTES, DATETIME, TIME, STRUCT, ARRAY, TIMESTAMP, etc.
Length – the third parameter is length. This is an optional parameter that specifies the length of the target type.

SQL Convert Varchar to Int

Consider the example below that converts varchar type to an int64 type.

SELECT CAST('344' AS INT64);

The example query should return the value converted to int as shown:

int4
------
344

SQL Convert Varchar To Numeric

To convert a varchar type to a numeric type, change the target type as numeric or BIGNUMERIC as shown in the example below:

SELECT CAST('344' AS NUMERIC) AS NUMERIC;
SELECT CAST('344' AS BIGNUMERIC) AS big_numeric;

The queries above should return the specified value converted to numeric and big numeric.

Note that to Ensure that your database engine supports your target type; otherwise, it will return an error.

SQL Convert Varchar To Float

If you have floating-point varchar, you can convert it to a float type, as shown in the example below:

SELECT CAST('3.14159' AS FLOAT) AS FLOAT;

The code should return the float type of the specified varchar. Example output is as shown below:

FLOAT
---------
3.14159
(1 ROW)

SQL Safe Casting

Have you wondered what happens if you try to convert a non-numeric string to a numeric type? Consider the example below:

SELECT CAST('hello' AS INT64) AS var;

If you attempt to run the query above, it will fail as the specified varchar type cannot be converted to int.

To prevent the query from failing if the conversion is not supported, we can use the SAFE_CAST method.

The function behaves similarly to the cast function, except it does not fail if the conversion fails. Instead, it returns null as shown in the example below:

SELECT SAFE_CAST('hello' AS INT64) AS NaN;

Instead of an error, the above query should return:

"NaN": NULL

NOTE: The SAFE_CAST method is supported in Standard SQL. Some database engines may implement the procedures differently. For example, in SQL Server, the safe_cast() function is renamed to try_cast or try_convert.

Conclusion

In this tutorial, we discussed how to perform type-casting in Standard SQL. For example, we discussed converting varchar to int64, numeric, bignumeric, and float64. We also covered how to perform safe casting for scenarios where the type-casting may result in an error.