EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL TEXT
 

SQL TEXT

Updated March 8, 2023

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - 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
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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 Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW