EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL BLOB

SQL BLOB

Updated March 8, 2023

SQL BLOB

Introduction to SQL BLOB

BLOB (Binary Large Object) is a data type in standard SQL used to store large amounts of data. It is basically a binary string of variable length, stored as a sequence of bytes or octets. BLOB data type is generally used to store large files such as images, media files such as video and audio clips in the database. We can write a BLOB value to the database as binary or character string depending on the file or requirement.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax and parameters:

The basic syntax for creating a database table with columns of BLOB data type is as follows:

CREATE TABLE tablename (
Column_name1 BLOB CONSTRAINT,
column_name2 data type CONSTRAINT,
.
.
.
);

The parameters used in the above mentioned syntax are similar to CREATE TABLE statement. The only difference being usage of BLOB data type instead of any other data type. We can simply use the keyword BLOB for using binary large object data type in MySQL and ORACLE databases. However, other databases such as PostgreSQL and SQL SERVER call binary large objects data types as BYTEA, binary instead.

Examples of SQL BLOB

Given below are the examples of SQL BLOB:

Example #1

SQL query to illustrate creation of a blob type field.

Code:

CREATE TABLE product_details_oracle
(
id INT PRIMARY KEY,
name VARCHAR(255),
description BLOB
);

Output:

SQL BLOB 1

The product_details_oracle table has been successfully created with a description field of BLOB data type.

Example #2

SQL query to illustrate value insertion in BLOB data type field.

Next let us insert a few records in the product_details_oracle using the following INSERT statements.

Code:

INSERT INTO product_details_oracle VALUES(1,'product12', utl_raw.cast_to_raw('This is a blob description'));

Output:

product_details_oracle

Code:

INSERT INTO product_details_oracle VALUES
(2,'product11', utl_raw.cast_to_raw ('This is a new blob description'));

Output:

SQL BLOB 4

We have successfully inserted two records in the table. In the insert statements mentioned above, you might have observed the utl_raw.cast_to_raw function. This function is basically used to convert values to raw data type.

Example #3

SQL query to illustrate value updation in BLOB data type field.

We can simply update a BLOB column using the generic update statement as shown below.

Code:

UPDATE table_name SET BLOBColumn = 'New Value' WHERE condition_expression;

But if you want to empty the blob storage and return the BLOB locator to initialize it further, you can use empty_blob() function. In this case, we have updated BLOB column for record with id = 2.

Code:

UPDATE product_details_oracle SET description = EMPTY_BLOB()
WHERE id = 2;

Output:

SQL BLOB 5

BLOB data type in other SQL databases:

What if you do not have access to ORACLE or MySQL databases but you still want to create a Binary Large Object data type. Here, we have used postgreSQL for illustration. In postgreSQL , we use BYTEA keyword instead of BLOB as shown in the following CREATE TABLE statement.

Code:

CREATE TABLE product_details(
id SERIAL PRIMARY KEY,
name VARCHAR(255),
description BYTEA
);

Output:

SQL BLOB 6

The command has successfully created the product_details table. Similar to the table in the oracle database, the description field has been kept as BYTEA type.

Next, let us insert a few records in the table. In MySQL and PostgreSQL, unlike ORACLE we do not require any hex_to_raw() or cast_to_raw() functions.

Code:

INSERT INTO product_details VALUES(1,'product12', ('This is a blob description'));

Output:

SQL BLOB 7

The record has been successfully inserted in the table. We observe from the data output of the SELECT statement that the description field has not been shown as text but has been converted into binary data.

Code:

SELECT * FROM product_details;

Output:

SQL BLOB 8

Let’s insert a few more records to work with BLOB data types.

Code:

INSERT INTO product_details VALUES(2,'product11','This is a new blob description'),
(3,'product12','This is a blob description'),
(4,'product11','This is a newer blob description');

Output:

insert a few more records

The inserted rows in the product_details table looks something as follows.

Code:

SELECT * FROM product_details;

Output:

SQL BLOB 11

Binary data in WHERE clause comparisons:

What if you want to compare BLOB or BYTEA data types in your SQL queries. We can use BLOB similar to other data types for comparison.

Example #4

Find the id, name and description for products that have exactly the same descriptions.

Code:

SELECT p1.id, p1.name, p1.description
FROM product_details as p1, product_details as p2
WHERE p1.description = p2.description  AND p1.id != p2.id;

Output:

exactly the same descriptions

In this query, we have tried to filter records by comparing description fields in the WHERE clause.

Note: Fields of BLOB data type can only be read in SQL databases. We cannot use database search engine functions like filtering and sorting for specific values within the BLOB values.

Advantages of using BLOB Data Type

  • BLOB are very convenient data types when it comes to storage of big data files such as movies, songs, TV shows etc. They can easily be encrypted and referenced.
  • However, not all SQL databases support BLOB data type. They require a large amount of storage space and hence have slower return time.

Conclusion

In this article we saw about BLOB data type. BLOB is a binary object data type in standard query language (SQL) that is used to store large files such as images, movies, audio clips etc in the database.

Recommended Articles

We hope that this EDUCBA information on “SQL BLOB” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SQL TRUNCATE()
  2. SQL WITH AS Statement
  3. SQL DML Commands
  4. Cursors in SQL
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
62+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
 204+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more