Examples Using TEXT and VARCHAR
Examples Let’s create an table using text data type
Suppose we have products table which is shown below is the table structure:
CREATE TABLE Products (
product_id INTEGER PRIMARY KEY,
product_name TEXT,
product_description TEXT
);
Example 1: Storing a Long Description
INSERT INTO Products (product_name, product_description)
VALUES ('Laptop', 'The latest laptop model featuring a high-resolution display, powerful processor, and long battery life. Perfect for productivity and entertainment.');
Output:
In this example, we use TEXT for both the product_name and product_description fields. The product_description field stores a lengthy description of the product which may vary in length for different products.
Example 2: Storing Multilingual Text
INSERT INTO Products (product_name, product_description)
VALUES ('Headphones', 'Premium headphones with noise-canceling technology. Des écouteurs de qualité supérieure avec une technologie de suppression de bruit.');
Output:
Here, we go through the flexibility of TEXT by storing product descriptions in multiple languages. The product_description field fits text data in various languages, including special characters and accent marks.
Example Using VARCHAR
CREATE TABLE Products2 (
product_id INTEGER PRIMARY KEY,
product_name VARCHAR(50),
product_description VARCHAR(200)
);
Example 1: Enforcing Maximum Length for Product Name
INSERT INTO Products2 (product_name, product_description)
VALUES ('Smartphone X1', 'A sleek and powerful smartphone with a high-definition display and advanced camera features.');
Output:
In this example, we use VARCHAR with a specified length for the product_name field. By setting a maximum length of 50 characters, we enforce constraints on the length of product names, ensuring consistency and preventing excessively long names.
Example 2: Optimizing Storage for Short Descriptions
INSERT INTO Products2 (product_name, product_description)
VALUES ('Tablet Y2', 'Compact tablet with fast performance and long battery life.');
Output:
Here, we utilize VARCHAR for the product_description field to optimize storage for relatively short descriptions. By specifying a maximum length of 200 characters, we reserve space efficiently for concise product descriptions while flexible variations in length.
SQLite TEXT vs VARCHAR
In SQLite, TEXT and VARCHAR are the fundamental building blocks used for storing textual information. TEXT is an advantage in that it can store as many characters as necessary without imposing restrictions on the maximum number and VARCHAR capacity to specify a maximum string length.
It also provides efficient storage utilization that can be suitable for columns that have relatively uniform string lengths, or when length constraints are necessary, such as usernames or email addresses. In this article, We will learn about SQLite TEXT vs VARCHAR with the help of examples in detail.