EDUCBA

EDUCBA

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

SQL TEXT

SQL TEXT

Introduction to SQL TEXT

TEXT is a variable width character string data type that supports non-Unicode data in the code page of a SQL database server and with a maximum string length of 2,147,483,647. This data type is used for storing large pieces of string data values. When data exceeds the specified threshold, the column containing text data type data values is stored out-of-row in a separate Large Object (LOB) data page. The row in the database table with the said column will have a 16-byte pointer to the LOB data page.

The TEXT data type is usually used to store long character string type data such as comments to this blog post, the content of this page in the source code, plain text or posts from tweets, Facebook posts, etc. Do not get overwhelmed by all the terms used here, we will be discussing the data type in detail in the subsequent sections.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax and parameters

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

CREATE TABLE table_name (
column_name_1 ID,
column_name_2 TEXT,
.
.
.
column_name_n TEXT
);

The parameters used in the above-mentioned syntax are as follows:

column_name_1, column_name_2: These are the names of columns or fields which we want to create with a specific data type. For example, column_name_2 and column_name_n can store values of TEXT data type.

Having discussed the syntax for creating columns that can store variable-length character type text, let us discuss a few examples illustrating its usage.

Examples

Here are the following examples mention below

Example #1 – Creating field names or column names with text data type

To illustrate working with the TEXT data type, let us create a dummy table called “feed_details” that contains details about posts made by users of a social media site and work with it. A simple CREATE statement for creating a feed_details table is as follows :

CREATE TABLE feed_details(
user_id int,
user_name varchar(255),
user_post TEXT,
comment_count INT
);

SQL TEXT output 1

We have successfully created the said table. Here “user_post” column provides for storing TEXT data type values. Now, let us insert a few records in it.

Example 2 – Inserting values into columns with TEXT data type

INSERT INTO public.feed_details(
user_id, user_name, user_post, comment_count)
VALUES (121,'UA12123',

‘TEXT is a variable-length character data type is
usually used to store long character string
type data such as comments to this blog post,
content of this page in the source code,
plain text or posts from tweets, Facebook posts, etc.’,301);

SQL TEXT output 2

The data record with TEXT data type looks something as follows in the feed_details table :

output 3

Example #3 – SQL query to use WHERE clause on TEXT data types

It is difficult to use a TEXT data type when performing comparisons. For example, have a look at the query given below.

SELECT * FROM feed_details
WHERE user_post = 'TEXT is a variable length'

output 4

The query returned successfully but it did not fetch any results. This query might throw errors in some database servers. So, you might have to convert the TEXT data type to VARCHAR(max). Now, observe the next query.

SELECT * FROM feed_details
WHERE user_post LIKE 'TEXT is a variable length%'

output 5

In the second case, the query returned successfully and it has fetched the desired result as well. Ergo, it is wiser to use wildcards such as LIKE to compare TEXT data types.

You might have observed in the INSERT query above and this example that values in the VARCHAR data type and TEXT data type are stored similarly, that is within a pair of quotations. Both the data types have storage attributes as “extended”. Your observation is to the point. Both data types are similar and are used to store values of character type.

Difference between VARCHAR and TEXT data types

To begin with, there is not much of a difference between VARCHAR and TEXT data types. As per documentation, both of them are variable-length arrays aka varlena in C data structures. Albeit, there are a few differences such as VARCHAR(n) provides room for limiting the maximum length of a VARCHAR field. For example, VARCHAR(255) will not allow you to insert character strings of more than 255 characters. There are no such limitations in TEXT. Most importantly, there is no performance difference between the data types.

However, developers and data engineers prefer to use TEXT in situations where we might have to store more data like paragraphs or long sentences and VARCHAR for columns where we want to store few words such as username and further use these columns for performing joins, as foreign key constraints and for indexing. TEXT is not a preferred type for indexing and joining tables because it cannot be fully part of an index. Albeit, we can always specify the prefix length for TEXT columns when using it for indexing.

Conclusion – SQL TEXT

TEXT datatype in SQL is used to store variable-length character string data values. It can store up to 1 Gb of data. It is used for storing long sentences and paragraph-like data values such as comments, social media feeds, text content of a web page in page source code, etc.

Recommended Articles

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

  1. SQL MID
  2. SQL GROUP BY Month
  3. PostgreSQL OR
  4. MySQL Split
MICROSOFT POWER BI Training
48+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
CYBER SECURITY & ETHICAL HACKING Certification Course
89+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
MICROSOFT AZURE
97+ Hours of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
KALI LINUX Certification Course
26+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
SQL - Everything in SQL
 253+ 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
  • Corporate Training
  • 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.

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
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

*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