Cast Various Types into an Int Data Type in SQL

Type conversion in SQL is as common in SQL as it is in any other programming language. SQL provides us with various methods and functions that we can use to convert from one type to another.

In this tutorial, we will learn about the methods and techniques we can use to convert various types into an int data type.

This can be particularly useful when we need to ensure that the data types are consistent or compatible with each other in the queries.

SQL Cast Function

One of the most common type conversion functions in SQL is the cast() function. This function allows us to provide an input type and the target type.

The function then converts the input value into the specified type as expressed in the following syntax:

CAST(expression AS target_type)

In this case, the expression represents the data that we wish to convert while the “target_type” specifies the data type to which we wish to convert.

Example 1:

A common conversion in SQL and other languages is converting a string or a VARCHAR type into its numerical or integer representation.

In SQL, we can quickly use the cast() function to quickly convert a given string representation to int.

Suppose we have a table as follows:

CREATE TABLE SAMPLE(VALUE VARCHAR(100));

INSERT

INTO

SAMPLE(VALUE)

VALUES ('100'),

('200'),

('300');

Suppose we wish to convert the “values” column that is stored as a string into int values. We can use the cast() function as demonstrated in the following example:

SELECT CAST(values AS int) AS values_int FROM sample;

This should convert the specified column from string into an int representation.

NOTE: MySQL does not support a direct cast using the cast() function. This is also applicable to the convert() function.

Example 2:

We can also use the convert() function to convert a given string to int. The function behaves closely similarly to the cast() function as follows:

SELECT Convert(values AS int) AS values_int FROM sample;

Conclusion

You can see here how to cast data types to integers from this demonstration.

Cast Various Types into an Int Data Type in SQL

SQL Cast Function

Example 1:

Example 2:

Conclusion

About the author

John Otieno