PostgreSQL: How to Use BETWEEN Clause with String Columns

Often in PostgreSQL you may want to use the BETWEEN clause to select only rows where the value in a string column is between two specific letters.

You can use the following syntax to do so:

SELECT *
FROM athletes
WHERE team>='A' and team<'D';

This particular example will select all rows from the athletes table where the string in the team column starts with either A, B or C.

Note: By using the syntax team<‘D’ we specify that the string must be “less than” D, which means it must start with a letter before D in the alphabet.

The following example shows how to use this syntax in practice.

Example: How to Use BETWEEN Clause with String Columns in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  athleteID INTEGER,
  team TEXT,
  points INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Atlanta', 22);
INSERT INTO athletes VALUES (0002, 'Boston', 30);
INSERT INTO athletes VALUES (0003, 'Brooklyn', 12);
INSERT INTO athletes VALUES (0004, 'Charlotte', 25);
INSERT INTO athletes VALUES (0005, 'Chicago', 31);
INSERT INTO athletes VALUES (0006, 'Cleveland', 16);
INSERT INTO athletes VALUES (0007, 'Dallas', 10);
INSERT INTO athletes VALUES (0008, 'Denver', 14);
INSERT INTO athletes VALUES (0009, 'Detroit', 20);

-- view all rows in table
SELECT * FROM athletes;

 athleteid |   team    | points 
-----------+-----------+--------
         1 | Atlanta   |     22
         2 | Boston    |     30
         3 | Brooklyn  |     12
         4 | Charlotte |     25
         5 | Chicago   |     31
         6 | Cleveland |     16
         7 | Dallas    |     10
         8 | Denver    |     14
         9 | Detroit   |     20

Suppose that we would like to select all rows in the table where the string in the team column starts with a letter between A and C.

We can use the following syntax to do so:

SELECT *
FROM athletes
WHERE team>='A' and team<'D';

 athleteid |   team    | points 
-----------+-----------+--------
         1 | Atlanta   |     22
         2 | Boston    |     30
         3 | Brooklyn  |     12
         4 | Charlotte |     25
         5 | Chicago   |     31
         6 | Cleveland |     16

Notice that this returns all rows where the string in the team column starts with a letter between A and C.

Feel free to use similar syntax to select rows where a string in a column starts with a letter between two specific letters in your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Numbers from a String
PostgreSQL: How to Pad Numbers with Leading Zeros
PostgreSQL: How to Extract Substring Before Specific Character