This article will see how to convert the column type to VARCHAR without losing the data.

Table of Contents:

We will get started by creating a sample table student_enroll_data.

CREATE TABLE student_enroll_data (
student_id INT,
student_name VARCHAR(50),
enroll_date DATE,
student_ssn_no INT,
fee_submitted DECIMAL(10,2)
);

Let us insert some data as well to the table student_enroll_data.

INSERT INTO student_enroll_data(student_id,student_name,enroll_date,student_ssn_no,fee_submitted) 
VALUES(1,"Daniel",'2021-12-12',1147483782,12378.90),
(2,"Sandy",'2021-10-12',1147483788,14578.90),
(3,"Ryma",'2021-11-22',1147483789,22378.90),
(4,"Jennifer",'2021-12-02',1147483790,12378.90),
(5,"Austin",'2021-11-12',1147483791,12378.90),
(6,"George",'2021-10-10',1147483792,12788.90),
(7,"Veronica",'2021-02-13',1147483793,12378.90);

View the snapshot of the table data by executing:

SELECT * FROM student_enroll_data;

Output:-

image_1

Change column type to VARCHAR without losing data : MODIFY clause

The below query is to modify the data type of column student_ssn_no from INT to VARCHAR.

ALTER TABLE student_enroll_data MODIFY student_ssn_no VARCHAR(50);

Action Output:-

image_2

Let us run the below query to confirm if the type is changed.

SELECT 
   COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE
    table_name = 'student_enroll_data';

Output:-

image_3

Output in image_3 shows that the datatype of student_ssn_no is now varchar with size 50. Let us verify if the data is preserved as well by executing:

SELECT * FROM student_enroll_data;

Output:-

image_4

Output in image_4 shows that the data is not lost.

READ MORE THROUGH: MySQL : Change Datatype of a Column

Change column type to VARCHAR without losing data : CHANGE clause

Observe the table definition below for the table student_enroll_data by executing:

 DESC student_enroll_data;

Output:-

image_5

Also, see the data in the table student_enroll_data by executing:

SELECT * FROM student_enroll_data;

Output:-

image_6

The below query is to change the datatype of column student_ssn_no from INT to VARCHAR.

ALTER TABLE student_enroll_data CHANGE student_ssn_no student_ssn_no VARCHAR(50);

Action Output:-

image_7

Verify the type of the column student_ssn_no by executing:

 DESC student_enroll_data;

Output:-

image_8
SELECT * FROM student_enroll_data;

Output:-

image_9

Output in image_9 shows that data is not lost.

We hope this article helped in changing the column data type to VARCHAR. Good Luck !!!
You need to add a widget, row, or prebuilt layout before you’ll see anything here. 🙂