How to Convert BLOB into VARCHAR in MySQL?

In this article, we would be learning a SQL query to convert a column of BLOB Data Type to VARCHAR Data Type. To execute this query we would need to alter the table and subsequently a column’s definition. We would first need to use the ALTER TABLE command to change the table.

ALTER TABLE: ALTER TABLE is a command used to add, delete, or modify columns in an existing table. It is also used to add and drop various constraints on an existing table. We have two commands for alter table MODIFY and CHANGE.

Step 1: Creating a database

To create a database using the following query

Query : 

CREATE DATABASE geek;

Step 2: Using the database

To use the database use the following query

Query : 

USE geek;

Step 3: Creating a table

Create a table having one of the columns with BLOB Data Type.

Query : 

CREATE TABLE demo_table(AGE INT,
PHOTO BLOB,
SALARY FLOAT);

Step 4: Describing the table

Use this query to describe the properties of a table.

Query : 

DESCRIBE demo_table;

Output:

Step 5: Changing column Data Type from BLOB to VARCHAR(100). We will be changing the PHOTO column as it is of the BLOB Data Type.

Method 1: USING MODIFY

To change a column definition use MODIFY along with the ALTER command.

Query : 

ALTER TABLE demo_table
MODIFY PHOTO VARCHAR(100);

Method 2: USING CHANGE 

With CHANGE, the syntax is a bit different. After the CHANGE keyword, you name the column you want to change, then specify the new definition, which includes the new name.

Query : 

ALTER TABLE demo_table CHANGE 
PHOTO PHOTO_CHANGE VARCHAR(100);

Step 6: Describe your table to see the changes 

DESCRIBE demo_table;

Output: Results after using MODIFY

Output: Results after using CHANGE