Cast INT in SQL is a must-know for anyone even remotely interested in data handling and analysis. This article will cover all the important cast INT topics.

Data type conversion is one of the essential tasks in data management and analysis. In this article, we’ll take a closer look at a very distinct conversion type – converting INT data type to other data types and vice versa.

We’ll explore some typical situations and pitfalls you can encounter when converting INT. There will also be several interview question examples to showcase how this conversion works.

What is Casting INT in SQL?

Casting or converting data types in SQL means changing a value from one data type to another.

Specifically, casting INT means converting the integer data type to another data type – typically a numerical one – and vice versa.

The standard SQL function for conversion supported in all databases is CAST() or  CAST AS SQL Function. Another function that does the same job is CONVERT(), but it’s SQL Server-specific.

Situations Where Casting INT is Necessary in SQL

In general, there are four typical situations where casting to or from INT will be necessary.

1. Data Calculations

It’s very common that performing, data calculations involves mathematical operations on two or more different numerical data types.

For example, the number of rides is stored as DECIMAL, the same as the number of passengers. If you want to calculate the average number of passengers by ride for each driver, you’d divide the number of passengers by the number of rides.

However, you should cast the values to DECIMAL before that, as you want to show this ratio as a decimal number. Otherwise, you’d end up with incorrect results, as multiplying an integer with a decimal results in an integer. In other words, you’d lose decimal points.

2. Data Standardization

Another example is when you want to ensure data types are consistent and logical, considering the data they should represent.

For instance, order IDs are often stored as INT. Usually, the discount rate would be stored as a numerical type. But it can also happen that it’s VARCHAR.

With this data being VARCHAR, you can’t do any mathematical operations, e.g., calculating the discount amount by multiplying the amount with the discount rate. So, it’s recommended to cast the discount rate to INT and make data ready for further calculations.

3. Transferring Data Between Systems

Different systems can use different data types for the same data. If you have to move data from one system to another, the data types have to match the goal system data type requirements. So, you’ll have to convert all integers to, say, FLOAT. Otherwise, you risk incompatibility, data loss, and corruption.

4. Error Prevention

Error, in this case, means type mismatch when incompatible types are used in operations or functions.

For example, you might want to concatenate the order ID with the hyphen and customer name to get, e.g., 1-Art Vandeley.

Sure, you can use CONCAT(). However, this won’t work because the order ID is an integer. So, you first need to convert it to VARCHAR, as CONCAT() works only with textual data. Only then can you proceed with concatenation.

Overview of the Steps Involved in Casting INT in SQL

Casting is not complicated and consists of four relatively simple steps. However, the most important is the first one; the other three steps are just technicalities.

1. Identify the Need for Casting: The most important step is to recognize the need for casting INT in SQL. If you don’t, your query won’t run or, even worse, it will return incorrect results.

2. Choose the Function for Casting: This step applies only to SQL Server users; they can choose between CAST() and CONVERT(). Users of other databases will have no choice but to use CAST().

3. Write the Query: In this step, you write the query that implements the casting function to cast to or from INT.

4. Check the Output: Don’t just accept the query output. Check if the conversion yielded the expected result and without errors.

Understanding Data Types in SQL

When casting INT in SQL, it’s important to know the characteristics and the use of the most common data types. Knowing that is crucial to determine, first, if the data conversion is necessary and, second, if the chosen data type fits the kind of data you want to show.

Here’s an overview of the most common data types.

When I talk about choosing the right data type for the data, this involves considering several things.

1. Size and Range: The data type must be able to store the required range of values. For example, don’t use CHAR, which has a maximum of 255 characters, if it’s highly likely your data will go beyond that. Use VARCHAR, which can go up to 65,535 characters.

2. Storage: You should also think about the storage space. In other words, if you know INT will suffice for sure, then don’t use BIGINT just because you can.

3. Precision: Considering precision is important when casting INT to another numerical type. Yes, you want decimals, but how precise should the values be? If you want exact values with the controlled number of decimal places to avoid rounding errors, then use DECIMAL. If you’re fine with approximate values, use FLOAT.

The Syntax for Casting INT in SQL

The CAST() syntax is as follows.

CAST(expression AS data_type)

The expression is data written in single quotes ('') or, more often, a column name you want to cast. After the keyword AS, you define the output data type.

In PostgreSQL, you can use a shorthand for CAST(), which is a double colon (::).

expression :: data_type

The SQL Server users might also want to use CONVERT(). It has a very similar, the-other-way-round syntax.

CONVERT(data_type, expression, [style])

So, here, you first define a data type and then refer to the data you’re converting. All the arguments are separated by a comma. CONVERT() also has the optional style parameter. It defines the format for the conversion. This is mainly used when converting date/time data types to and from string data types, so you won’t be needing this when converting INT.

Comparison of CAST() and CONVERT() for Type Conversion

As CAST() and CONVERT() do exactly the same thing, let’s see how to use either of them in a simple example.

Example #1: Casting to INT

This question by Yelp explicitly asks to cast one column, namely stars, to INT. Apart from that, all other columns from the table yelp_reviews should be shown, and the non-integer values from the column stars should be removed.

Link to the question: https://platform.stratascratch.com/coding/10056-cast-stars-column-values-to-integer-and-return-with-all-other-column-values

Here’s the dataset preview.

The column stars, which we have to convert to INT, is defined as VARCHAR.

Solving this question using CAST() means that we reference the column stars in the function and define the output data type as INT.

In addition, we can exclude the question mark character from the same column, as it is not an integer.

How do we know there are no other non-integer characters in this column? Well, the question allowed us to explore the dataset before writing a solution.

SELECT business_name,
       review_id,
       user_id,
       CAST(stars AS INT) AS cast_stars,
       review_date,
       review_text,
       funny,
       useful,
       cool
FROM yelp_reviews
WHERE stars <> '?';

Here you have the output.

CAST() has the same syntax in all databases, as it’s ANSI standard. In addition, PostgreSQL allows the use of a double colon operator instead of CAST(), so casting is even simpler.

The output is, of course, the same.

In SQL Server, you have the option to use CONVERT() instead of CAST(). In that case, the output data type is defined first, and then the column stars is referenced.

SELECT business_name,
       review_id,
       user_id,
       CONVERT(INT, stars) AS cast_stars,
       review_date,
       review_text,
       funny,
       useful,
       cool
FROM yelp_reviews
WHERE stars <> '?';

You can see that CONVERT() returns the same output as CAST().

However, you really can’t see from the output that the new data type is INT. Let’s check this by adding one column that uses the pg_typeof function to the PostgreSQL solution to show the converted data type of the column stars.

SELECT business_name,
       review_id,
       user_id,
       CAST(stars AS INT) AS cast_stars,
       pg_typeof(CAST(stars AS INT)) AS cast_stars_type,
       review_date,
       review_text,
       funny,
       useful,
       cool
FROM yelp_reviews
WHERE stars <> '?';

As you can see from the output, the column stars is really cast as INT.

Example #2: Casting INT in Calculations

Let’s see another example where it’s necessary to cast INT to another numerical data type before the mathematical operation is performed.

This question by Airbnb is ideal for showcasing that.

Link to the question: https://platform.stratascratch.com/coding/9624-accommodates-to-bed-ratio

The question asks to calculate the average accommodates-to-beds ratio for shared rooms in each city and sort the output from the highest to the lowest-ratio city.

We will use the table airbnb_search_details.

Let’s not immediately jump to the question solution.

We’ll explore it step by step. The approach is to divide the column accommodates with the column beds to get accommodates-to-beds ratio. Explore the dataset and you’ll notice we’re dividing integers. As a result, the output will also be an integer.

Typically, ratios are shown as a decimal number. Because of this, we need to cast INT. It’s sufficient to cast only one integer.

Let’s translate all this into an SQL query.

SELECT city,
       CAST(accommodates AS FLOAT) / beds AS crowdness_ratio
FROM airbnb_search_details
WHERE room_type='Shared room'
GROUP BY city, crowdness_ratio
ORDER BY crowdness_ratio DESC;

Cool! So, the output shows different ratios for LA and NYC. The data conversion obviously succeeded, as we have two ratios shown as decimals.

What would have happened if we ignored the need for casting?

SELECT city,
       accommodates / beds AS crowdness_ratio
FROM airbnb_search_details
WHERE room_type='Shared room'
GROUP BY city, crowdness_ratio
ORDER BY crowdness_ratio DESC;

Three rows would be exactly the same, but that’s only by chance, as the division returns a whole number in those cases.

However, the last two rows show different results from the previous output. Instead of 0.5 and 0.4, we have 0.

Why is that? Dividing integers truncates the result towards zero.

So, in this first step, we already have incorrect results.

The error will be compounded when we write the final code solution, as we also need to calculate the average of these ratios. An average based on wrong values? It doesn’t sound promising!

Let’s return to our code, where we use CAST(). If I amend it like this, it becomes a question solution.

So, the only changes are:

1. Using AVG() on the cast ratio calculation.
2. Renaming the output column from crowdness_ratio to avg_crowdness_ratio.
3. Removing the column crowdness_ratio from GROUP BY as this column is now aggregated.

SELECT city,
       AVG(CAST(accommodates AS FLOAT)/ CAST(beds AS FLOAT)) AS avg_crowdness_ratio
FROM airbnb_search_details
WHERE room_type='Shared room'
GROUP BY city
ORDER BY avg_crowdness_ratio DESC;

The output shows the average ratio for LA is 1.6, and for NYC, it’s 1.5.

Now, the same code without casting looks like this.

SELECT city,
       AVG(accommodates / beds) AS avg_crowdness_ratio
FROM airbnb_search_details
WHERE room_type='Shared room'
GROUP BY city
ORDER BY avg_crowdness_ratio DESC;

The code calculates the LA ratio at 1.5 (wrong!) and NYC at 1.333 (again, wrong!). You see now why casting INT is important!

Of course, the question can also be solved using CONVERT() in SQL Server.

SELECT city,
       AVG(CONVERT(FLOAT, accommodates)/ CONVERT(FLOAT, beds)) AS avg_crowdness_ratio
FROM airbnb_search_details
WHERE room_type='Shared room'
GROUP BY city
ORDER BY avg_crowdness_ratio DESC;

As expected, the output is the same as that of CAST().

Handling NULL Values When Casting INT in SQL

When casting INT, the NULL values can be handled in two ways:

1. Implicitly: Let CAST() or CONVERT() handle it, which means they will automatically return NULL where there are NULL values.

2. Explicitly: You could use COALESCE() to check for NULL values and specify the value with which they’ll be replaced.

Example #3: Handling NULLs Implicitly

I’ll use this easy question by ESPN to show you how to handle NULLs.

Link to the question: https://platform.stratascratch.com/coding/10184-order-all-countries-by-the-year-they-first-participated-in-the-olympics

We’re given the table olympics_athletes_events

Let’s imagine you need to cast the age column to INT. You would do it like this.

SELECT id,
       CAST(age AS INT) AS cast_age
FROM olympics_athletes_events;

Since there are also NULLs in the age column, those values will be displayed as NULL despite casting.

Example #4: Handling NULLs Explicitly

But what if you’re moving data between systems, and the destination system requires the age data to be INT but also doesn’t accept NULLs in that column?

In that case, you should use COALESCE() with CAST().

The code below is similar to the previous one. However, I use COALESCE() to replace all NULLs with zeros.

SELECT id,
       COALESCE(CAST(age AS INT),0) AS cast_age
FROM olympics_athletes_events;

If you compare the output with the previous one, you’ll see there are 0s where there were once NULLs.

Best Practices for Type Conversion in SQL

When casting data types, I recommend you stick to these four simple and common-sense rules.

1. Using CAST() for Compatibility

As you already know, CAST() is part of the ANSI SQL standard. Stick to it if you plan to use SQL code across different RDBMSs, as they all support CAST().

If you write your code in SQL Server and use CONVERT(), you’ll have to rewrite it if you want to run it in another SQL flavor.

2. Handle NULLs Appropriately

Having NULLs in your dataset can lead to unexpected results or errors. For example, this can happen when doing arithmetic operations, aggregating data, filtering, or sorting data.

So, use the standard SQL function COALESCE() to provide default values for NULLs or ISNULL() in SQL Server and IFNULL() in MySQL.

3. Check the Conversion Result

Converting data types can result in errors or unexpected output. This is especially true when you write more complex queries.

Whenever you convert types, check the output and validate if this is the expected result. Also, test it on edge cases to see if it covers possible scenarios that are maybe not present in the current data.

4. Understand Database Specifics

Whatever SQL database you use, read its documentation. All the specific data type conversion functions, their default behaviors, and conversion quirks will be explained there.

For example, PostgreSQL allows using :: instead of CAST(). On the other hand, in SQL Server you have an option of using CONVERT(), which might be useful when wanting more control over date and time formats.

Conclusion

Today, we discussed one specific data type conversion: from and to INT. This conversion works similarly to any other data type conversion in SQL, as it most typically involves the standard SQL function CAST().

I’ve shown you how this works in several practical examples. However, casting INT in SQL is technically relatively simple. Where this gets complicated is recognizing that the casting is needed and deciding which data type to choose. We discussed different data types to help you with this.

But all this can’t be learned only by reading an article, no matter how good it is. So, your next step is to practice by solving at least several of many coding interview questions from our platform that require data type casting.

This is an important step in avoiding data handling errors that could occur by not casting data types or casting them to inadequate data types.