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
CREATE DATABASE geek;
Step 2: Using the database
To use the database use the following query
Step 3: Creating a table
Create a table having one of the columns with BLOB Data Type.
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.
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.
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.
ALTER TABLE demo_table CHANGE PHOTO PHOTO_CHANGE VARCHAR(100);
Step 6: Describe your table to see the changes
Output: Results after using MODIFY
Output: Results after using CHANGE