SQL BETWEEN vs LIKE – Powerful Filtering Explained

Introduction

In SQL, text data types are used to store alphanumeric values like names, addresses, emails, and descriptions. Choosing the correct text type — CHAR, VARCHAR, or TEXT — is important for optimizing storage space, query speed, and database performance.

In this section, you'll learn the definitions, differences, and best use cases for each text data type.

1. CHAR (Fixed-Length String)

CHAR is used to store fixed-length strings. If the stored string is shorter than the defined length, SQL automatically pads it with spaces to match the specified size.

Features:

• Fixed length
• Fast and predictable performance
• Uses extra storage if the data is often shorter than the specified length

Syntax:

column_name CHAR(length);

length = number of characters (1 to 255 depending on the database system)

Example:

CREATE TABLE countries (
  country_code CHAR(2),
  country_name CHAR(50)
);

country_code like 'US', 'IN', 'UK' will always take 2 characters.

When to Use CHAR:

• Data with a constant size, such as country codes, gender ('M', 'F'), state abbreviations
• Fixed-format fields like credit card types ('VISA', 'MC')
• When exact storage size is known and consistent

2. VARCHAR (Variable-Length String)

VARCHAR stands for Variable Character. It stores variable-length strings, meaning only the actual characters are stored without unnecessary padding.

Features:

• Variable length
• More space-efficient than CHAR for varying-length text
• Slightly slower than CHAR when processing large volumes (because of extra calculations for string lengths)

Syntax:

column_name VARCHAR(length);

length = maximum number of characters allowed

Example:

CREATE TABLE employees (
  first_name VARCHAR(50),
  email VARCHAR(100)
);

Names and emails can vary in length, making VARCHAR ideal.

When to Use VARCHAR:

• Data with unpredictable or variable length
• Names, emails, addresses, and descriptions under 255-65535 characters
• Most general-purpose text fields

3. TEXT (Large Text Field)

TEXT is used to store large amounts of text like long descriptions, blog posts, comments, or articles.

Features:

• Meant for large text storage (up to 65,535 characters for standard TEXT in MySQL)
• Cannot have a default value (in some databases like MySQL)
• TEXT fields are stored outside the main table with a pointer reference
• Different variants exist (TINYTEXT, MEDIUMTEXT, LONGTEXT) for various sizes

Syntax:

column_name TEXT;

Example:

CREATE TABLE articles (
  id INT,
  title VARCHAR(255),
  body TEXT
);

body will store the full article content, which can be very large.

When to Use TEXT:

• Long-form text fields (comments, articles, reviews, reports)
• Data that exceeds normal VARCHAR limits
• When exact storage requirements are unknown or potentially very large

Quick Comparison: CHAR vs VARCHAR vs TEXT

Important Tips

• Use CHAR only when all values will be exactly the same length
• VARCHAR is the best choice for most standard text fields
• Reserve TEXT for content that exceeds VARCHAR limits
• Consider VARCHAR(MAX) in SQL Server for large text that might need indexing
• Be aware that TEXT fields may have limitations on default values and full-text indexing

BETWEEN vs LIKE Operators

Introduction

Both BETWEEN and LIKE are used in SQL's WHERE clause for filtering data — but they serve different purposes.

• BETWEEN: Filters numerical, date, or text values within a range.
• LIKE: Filters text values using pattern matching with wildcards.

Let's explore both with syntax, examples, and use cases.

1. BETWEEN – Filter by Range

The BETWEEN operator is used to select values within a range, including the start and end values.

Syntax:

SELECT * FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example 1: Numbers

SELECT * FROM products
WHERE price BETWEEN 100 AND 500;

Returns all products priced from 100 to 500, inclusive.

Example 2: Dates

SELECT * FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-03-31';

Retrieves all orders placed between January 1 and March 31, 2025.

2. LIKE – Filter by Pattern

The LIKE operator is used to search for a specified pattern in a column.

Wildcards:

• %: Matches any number of characters
• _: Matches a single character

Syntax:

SELECT * FROM table_name
WHERE column_name LIKE 'pattern';

Example 1: Starts With

SELECT * FROM customers
WHERE name LIKE 'A%';

Finds all names that start with 'A'.

Example 2: Ends With

SELECT * FROM products
WHERE name LIKE '%phone';

Finds names that end with 'phone', like "iPhone", "Smartphone".

Example 3: Contains

SELECT * FROM users
WHERE email LIKE '%gmail%';

Finds any email that contains 'gmail'.

3. Combine BETWEEN and LIKE

You can combine both in a single query using AND:

SELECT * FROM books
WHERE publish_year BETWEEN 2015 AND 2020
AND title LIKE '%SQL%';

Retrieves books published between 2015-2020 with "SQL" in the title.

Summary Table

Conclusion

• Use BETWEEN when you need to filter by a continuous range of numbers, dates, or values.
• Use LIKE when you want to search for patterns in text, such as names, emails, or descriptions.