What Are the Steps to Cast INT in SQL for Type Conversion?

What Are the Steps to Cast INT in SQL


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.

Situations Where Casting Int Is Necessary in Sql

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.

Casting Int in Sql is Important to Understand 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.


Table: yelp_reviews


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.

Table: yelp_reviews
business_namereview_iduser_idstarsreview_datereview_textfunnyusefulcool
AutohausAZC4TSIEcazRay0qIRPeMAFgjlbPUcCRiXlMtarzi9sW5w52011-06-27Autohaus is my main source for parts for an old Mercedes that has been in the family since new that I am restoring. The old beater is truly a labor of121
Citizen Public HouseZZ0paqUsSX-VJbfodTp1cQEeCWSGwMAPzwe_c1Aumd1w42013-03-18First time in PHX. Friend recommended. Friendly waitstaff. They were understaffed and extremely busy, but we never knew. The short ribs were tende000
Otto Pizza & PastrypF6W5JOPBK6kOXTB58cYrwJG1Gd2mN2Qk7UpCqAUI-BQ52013-03-14LOVE THIS PIZZA! This is now one of my favorite pizza places in phoenix area. My husband i walked into this cute family owned business and were greet000
Giant HamburgersQBddRcflAcXwE2qhsLVv7wT90ybanuLhAr0_s99GDeeg32009-03-27ok, so I tried this place out based on other reviews. I had the cheeseburger with everything, some fries, and a chocolate shake. The burger was okay, 011
Tammie Coe CakesY8UMm_Ng9oEpJbIygoGbZQMWt24-6bfv_OHLKhwMQ0Tw32008-08-25Overrated. The treats are tasty but certainly not the best I have ever had. I would have rated this a two star but the cakes and cookies are REALLY pr132

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.

All required columns and the first 5 rows of the solution are shown

business_namereview_iduser_idstarsreview_datereview_textfunnyusefulcool
AutohausAZC4TSIEcazRay0qIRPeMAFgjlbPUcCRiXlMtarzi9sW5w52011-06-27Autohaus is my main source for parts for an old Mercedes that has been in the family since new that I am restoring. The old beater is truly a labor of121
Citizen Public HouseZZ0paqUsSX-VJbfodTp1cQEeCWSGwMAPzwe_c1Aumd1w42013-03-18First time in PHX. Friend recommended. Friendly waitstaff. They were understaffed and extremely busy, but we never knew. The short ribs were tende000
Otto Pizza & PastrypF6W5JOPBK6kOXTB58cYrwJG1Gd2mN2Qk7UpCqAUI-BQ52013-03-14LOVE THIS PIZZA! This is now one of my favorite pizza places in phoenix area. My husband i walked into this cute family owned business and were greet000
Giant HamburgersQBddRcflAcXwE2qhsLVv7wT90ybanuLhAr0_s99GDeeg32009-03-27ok, so I tried this place out based on other reviews. I had the cheeseburger with everything, some fries, and a chocolate shake. The burger was okay, 011
Tammie Coe CakesY8UMm_Ng9oEpJbIygoGbZQMWt24-6bfv_OHLKhwMQ0Tw32008-08-25Overrated. The treats are tasty but certainly not the best I have ever had. I would have rated this a two star but the cakes and cookies are REALLY pr132

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().

All required columns and the first 5 rows of the solution are shown

business_namereview_iduser_idstarsreview_datereview_textfunnyusefulcool
AutohausAZC4TSIEcazRay0qIRPeMAFgjlbPUcCRiXlMtarzi9sW5w52011-06-27Autohaus is my main source for parts for an old Mercedes that has been in the family since new that I am restoring. The old beater is truly a labor of121
Citizen Public HouseZZ0paqUsSX-VJbfodTp1cQEeCWSGwMAPzwe_c1Aumd1w42013-03-18First time in PHX. Friend recommended. Friendly waitstaff. They were understaffed and extremely busy, but we never knew. The short ribs were tende000
Otto Pizza & PastrypF6W5JOPBK6kOXTB58cYrwJG1Gd2mN2Qk7UpCqAUI-BQ52013-03-14LOVE THIS PIZZA! This is now one of my favorite pizza places in phoenix area. My husband i walked into this cute family owned business and were greet000
Giant HamburgersQBddRcflAcXwE2qhsLVv7wT90ybanuLhAr0_s99GDeeg32009-03-27ok, so I tried this place out based on other reviews. I had the cheeseburger with everything, some fries, and a chocolate shake. The burger was okay, 011
Tammie Coe CakesY8UMm_Ng9oEpJbIygoGbZQMWt24-6bfv_OHLKhwMQ0Tw32008-08-25Overrated. The treats are tasty but certainly not the best I have ever had. I would have rated this a two star but the cakes and cookies are REALLY pr132

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.

All required columns and the first 5 rows of the solution are shown

business_namereview_iduser_idcast_starscast_stars_typereview_datereview_textfunnyusefulcool
AutohausAZC4TSIEcazRay0qIRPeMAFgjlbPUcCRiXlMtarzi9sW5w5integer2011-06-27Autohaus is my main source for parts for an old Mercedes that has been in the family since new that I am restoring. The old beater is truly a labor of121
Citizen Public HouseZZ0paqUsSX-VJbfodTp1cQEeCWSGwMAPzwe_c1Aumd1w4integer2013-03-18First time in PHX. Friend recommended. Friendly waitstaff. They were understaffed and extremely busy, but we never knew. The short ribs were tende000
Otto Pizza & PastrypF6W5JOPBK6kOXTB58cYrwJG1Gd2mN2Qk7UpCqAUI-BQ5integer2013-03-14LOVE THIS PIZZA! This is now one of my favorite pizza places in phoenix area. My husband i walked into this cute family owned business and were greet000
Giant HamburgersQBddRcflAcXwE2qhsLVv7wT90ybanuLhAr0_s99GDeeg3integer2009-03-27ok, so I tried this place out based on other reviews. I had the cheeseburger with everything, some fries, and a chocolate shake. The burger was okay, 011
Tammie Coe CakesY8UMm_Ng9oEpJbIygoGbZQMWt24-6bfv_OHLKhwMQ0Tw3integer2008-08-25Overrated. The treats are tasty but certainly not the best I have ever had. I would have rated this a two star but the cakes and cookies are REALLY pr132

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.


Table: airbnb_search_details

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.

Table: airbnb_search_details
idpriceproperty_typeroom_typeamenitiesaccommodatesbathroomsbed_typecancellation_policycleaning_feecityhost_identity_verifiedhost_response_ratehost_sinceneighbourhoodnumber_of_reviewsreview_scores_ratingzipcodebedroomsbeds
12513361555.68ApartmentEntire home/apt{TV,"Wireless Internet","Air conditioning","Smoke detector","Carbon monoxide detector",Essentials,"Lock on bedroom door",Hangers,Iron}21Real BedflexibleFALSENYCt89%2015-11-18East Harlem3871002901
7196412366.36CabinPrivate room{"Wireless Internet",Kitchen,Washer,Dryer,"Smoke detector","First aid kit","Fire extinguisher",Essentials,"Hair dryer","translation missing: en.hosting_amenity_49","translation missing: en.hosting_amenity_50"}23Real BedmoderateFALSELAf100%2016-09-10Valley Glen14919160611
16333776482.83HousePrivate room{TV,"Cable TV",Internet,"Wireless Internet",Kitchen,"Free parking on premises","Pets live on this property",Dog(s),"Indoor fireplace","Buzzer/wireless intercom",Heating,Washer,Dryer,"Smoke detector","Carbon monoxide detector","First aid kit","Safety card","Fire extinguisher",Essentials,Shampoo,"24-hour check-in",Hangers,"Hair dryer",Iron,"Laptop friendly workspace","translation missing: en.hosting_amenity_49","translation missing: en.hosting_amenity_50","Self Check-In",Lockbox}21Real BedstrictTRUESFt100%2013-12-26Richmond District117969411811
1786412448.86ApartmentPrivate room{"Wireless Internet","Air conditioning",Kitchen,Heating,"Suitable for events","Smoke detector","Carbon monoxide detector","First aid kit","Fire extinguisher",Essentials,Shampoo,"Lock on bedroom door",Hangers,"translation missing: en.hosting_amenity_49","translation missing: en.hosting_amenity_50"}21Real BedstrictTRUENYCt93%2010-05-11Williamsburg8861121111
14575777506.89VillaPrivate room{TV,Internet,"Wireless Internet","Air conditioning",Kitchen,"Free parking on premises",Essentials,Shampoo,"translation missing: en.hosting_amenity_49","translation missing: en.hosting_amenity_50"}62Real BedstrictTRUELAt70%2015-10-2221009070333

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.

All required columns and the first 5 rows of the solution are shown

citycrowdness_ratio
LA4
NYC2
LA1
NYC0.5
LA0.4

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.

All required columns and the first 5 rows of the solution are shown

citycrowdness_ratio
LA4
NYC2
LA1
LA0
NYC0

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.

All required columns and the first 5 rows of the solution are shown

cityavg_crowdness_ratio
LA1.6
NYC1.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!

All required columns and the first 5 rows of the solution are shown

cityavg_crowdness_ratio
LA1.5
NYC1.333

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().

All required columns and the first 5 rows of the solution are shown

cityavg_crowdness_ratio
LA1.6
NYC1.5

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.


Table: olympics_athletes_events

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

Table: olympics_athletes_events
idnamesexageheightweightteamnocgamesyearseasoncitysporteventmedal
3520Guillermo J. AmparanMMexicoMEX1924 Summer1924SummerParisAthleticsAthletics Men's 800 metres
35394Henry John FinchettMGreat BritainGBR1924 Summer1924SummerParisGymnasticsGymnastics Men's Rings
21918Georg Frederik Ahrensborg ClausenM28DenmarkDEN1924 Summer1924SummerParisCyclingCycling Men's Road Race Individual
110345Marinus Cornelis Dick SigmondM26NetherlandsNED1924 Summer1924SummerParisFootballFootball Men's Football
54193Thodore Tho JeitzM26LuxembourgLUX1924 Summer1924SummerParisGymnasticsGymnastics Men's Individual All-Around

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.

All required columns and the first 5 rows of the solution are shown

idcast_age
3520
35394
2191828
11034526
5419326

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.

All required columns and the first 5 rows of the solution are shown

idcast_age
35200
353940
2191828
11034526
5419326

Best Practices for Type Conversion in SQL

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

Best Practices to Cast Int in Sql for Type Conversion

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.

What Are the Steps to Cast INT in SQL


Become a data expert. Subscribe to our newsletter.