How to Convert From BLOB to Text in MySQL?

In this article, we will see the conversion of a BLOB to TEXT in MySQL.

BLOB: It stands for Binary Large Object. It is a kind of data type in MySQL that can store files or images in the database in binary format.  It has four types i.e TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. All four types are similar, the only difference among them is the amount of data they can hold. 

AS the name suggests, LONGBLOB can hold the maximum amount of data and TINYBLOB can hold the least amount of data among the four types.

TEXT datatype in MySQL is used for storing long text strings in the database. It is just like VARCHAR. In order to convert BLOB to TEXT, we will use the CONVERT statement.

Syntax:

CONVERT( column_name using utf8);

utf8 is the way of encoding Unicode characters. It is recommended to use ut8 while creating web pages and databases. For demonstration, follow the below steps:

Step 1: Create a database

we can use the following command to create a database called geeks.

Query:

CREATE DATABASE geeks;

Step 2: Use database

Use the below SQL statement to switch the database context to geeks:

Query:

USE geeks;

Step 3: Table definition

We have demo_table in our geek’s database.

Query:

 CREATE TABLE demo_table(
   NAME VARCHAR(20),
   AGE INT,
  CITY VARCHAR(20),
   FILE BLOB);

Step 4: Insert data into a table

Query:

 INSERT INTO demo_table VALUES ('Romy', 21, 
 'Delhi', 'My name is romy kumari,
 I am 21 yrs old'),
('Pushkar', 22, 'Delhi', 
'My name is Pushkar jha,
 I am 22 yrs old'),
 ('Rinkle', 22, 'Punjab',
  'My name is Rinkle Arora,
  I am 22 yrs old'),
('Ayushi', 22, 'Patna', 'My name is
 Ayushi choudhary, I am 22 yrs old');

Step 5: View the content

Execute the below query to see the content of the table

Query:

SELECT * FROM demo_table;

Output:

We can see that content of the FILE column is in encoded format.

Step 6: Conversion from BLOB to TEXT.

Query:

SELECT convert(File using utf8)
from demo_table;

If you want to update the BLOB datatype column to the TEXT datatype column. Follow these steps:

• Alter the table and add a column having data type TEXT.
• Add content to that column after converting BLOB data to TEXT date.
• Drop BLOB column.

Step 1: Add column

Syntax:

ALTER Table demo_table ADD 
COLUMN AFTER_CONERSION TEXT;

Step 2: Add content to column

 UPDATE demo_table SET AFTER_CONERSION 
 = CONVERT (FILE using utf8);

Step 3: Drop BLOB column

 ALTER TABLE demo_table 
 DROP COLUMN FILE;

romy421kumari

Follow

Improve

Article Tags :

• SQL
• mysql
• SQL-Query