• Ekta Aggarwal

CAST function in SQL

In SQL, CAST function is used to change the datatype of an already existing column to a specified data type.

Syntax:

CAST (already existing column AS new data type)


Task: Change the format of 3.2 to decimal(5,2) and integer.

SELECT CAST(3.2 as decimal(5,2)) as decimal_conversion,
CAST(3.2 as int) as int_conversion;

Dataset:


For this tutorial we shall make use of employee_performance:

CREATE TABLE employee_performance(
employee_id int,
department varchar(20),
education_level varchar(20) ,
gender char(1),
no_of_trainings int,
age int,
previous_year_rating numeric(2,1),
length_of_service int,
KPIs_met char(1),
avg_training_score numeric(5,2),
promoted_or_not varchar(3))

INSERT INTO
employee_performance(employee_id,department,education_level,gender,no_of_trainings,age,previous_year_rating,length_of_service,KPIs_met,avg_training_score,promoted_or_not)
VALUES
(1001,'Marketing','Graduate','M',2,24,NULL,1,'Y',69.5,'N'),
(1002,'Analytics','Post-Graduate','M',5,32,4.5,7,'Y',89.5,'Y'),
(1003,'R&D','Graduate','F',5,44,5,10,'Y',87,'Y'),
(1004,'HR','Graduate','M',1,32,3,3,'Y',54,'N'),
(1005,'Marketing','PhD','M',0,38,4,7,'N',79.5,'N'),
(1006,'IT','Graduate','F',2,23,NULL,0,'Y',83.5,'N'),
(1007,'Analytics','Post-Graduate','M',2,28,5,4,'Y',78.5,'Y');

Our data looks as follows:

SELECT employee_id,previous_year_rating
FROM employee_performance 

Task: Convert previous_year_rating to integer.

SELECT employee_id,previous_year_rating, CAST(previous_year_rating as int) as cast_output
FROM employee_performance

Tags: