Often in PostgreSQL you may want to check if a string contains any alphabetical letters.
You can use the following methods to do so:
Method 1: Select All Rows that Contain Letters
SELECT *
FROM products
WHERE product_label ~* '[a-z]';
This particular example will return all rows from the products table where the string in the product_label column contains at least one alphabetical letter.
Note: The syntax ~* is case-insensitive and will match all lowercase and uppercase letters.
Method 2: Select All Rows that Do Not Contain Letters
SELECT *
FROM products
WHERE (product_label ~* '[a-z]') is false;
This particular example will return all rows from the products table where the string in the product_label column does not contain any alphabetical letters.
The following example shows how to use each of these methods in practice.
Example: How to Check if String Contains Any Letters in PostgreSQL
Suppose that we create the following table named products that contains information about various products sold at some retail store:
-- create table named 'products'
CREATE TABLE products (
id INTEGER PRIMARY KEY,
product_label TEXT,
date_sold DATE
);
-- add rows to the table
INSERT INTO products VALUES (0001, '455622', '2024-09-11');
INSERT INTO products VALUES (0002, 'A00933', '2024-09-12');
INSERT INTO products VALUES (0003, '2298.1', '2024-09-10');
INSERT INTO products VALUES (0004, '345.07', '2024-09-15');
INSERT INTO products VALUES (0005, 'CHYYGG', '2024-09-18');
INSERT INTO products VALUES (0006, 'C74359', '2024-09-22');
-- view all rows in table
SELECT * FROM products;
id | product_label | date_sold
----+---------------+------------
1 | 455622 | 2024-09-11
2 | A00933 | 2024-09-12
3 | 2298.1 | 2024-09-10
4 | 345.07 | 2024-09-15
5 | CHYYGG | 2024-09-18
6 | C74359 | 2024-09-22
We can use the following syntax to only select rows where the string in the product_label column contains at least one alphabetical letter:
SELECT *
FROM products
WHERE product_label ~* '[a-z]';
id | product_label | date_sold
----+---------------+------------
2 | A00933 | 2024-09-12
5 | CHYYGG | 2024-09-18
6 | C74359 | 2024-09-22
Notice that the resulting table returns all rows where the string in the product_label column contains at least one alphabetical letter.
We could instead use the following syntax to only select rows where the string in the product_label column does not contain any alphabetical letters:
SELECT *
FROM products
WHERE (product_label ~* '[a-z]') is false;
id | product_label | date_sold
----+---------------+------------
1 | 455622 | 2024-09-11
3 | 2298.1 | 2024-09-10
4 | 345.07 | 2024-09-15
We can see that this returns all rows in the table where the string in the product_label column does not contain any alphabetical letters.
Feel free to use similar syntax to check if strings contain letters in a column of 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
