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.

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

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

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

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'

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

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

This is a guide to SQL TEXT. Here we discuss the Examples and the Difference between VARCHAR and TEXT data types. You may also have a look at the following articles to learn more –

1. SQL MID
2. SQL GROUP BY Month
3. PostgreSQL OR
4. MySQL Split