Often in PostgreSQL you may want to only select rows where a specific column contains a number.
You can use the following syntax to do so:
SELECT *
FROM products
WHERE name ~ '[0-9]+';
This particular example will select the rows in the products table where the value in the name column contains at least one number.
Note that we use the ~ command to select rows that match a particular pattern. We specify [0-9]+ to match one or more numbers.
The following example shows how to use this syntax in practice.
Example: Select Rows where Column Contains Number 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,
name TEXT,
date_sold DATE
);
-- add rows to the table
INSERT INTO products VALUES (0001, 'A0045BH', '2024-09-11');
INSERT INTO products VALUES (0002, 'A00933', '2024-09-12');
INSERT INTO products VALUES (0003, 'BRRHHY', '2024-09-10');
INSERT INTO products VALUES (0004, 'BR3350', '2024-09-15');
INSERT INTO products VALUES (0005, 'CHYYGG', '2024-09-18');
INSERT INTO products VALUES (0006, 'C743559', '2024-09-22');
-- view all rows in table
SELECT * FROM products;
id | name | date_sold
----+---------+------------
1 | A0045BH | 2024-09-11
2 | A00933 | 2024-09-12
3 | BRRHHY | 2024-09-10
4 | BR3350 | 2024-09-15
5 | CHYYGG | 2024-09-18
6 | C743559 | 2024-09-22
Suppose that we would like to select only the rows that contain at least one number in the name column.
We can use the following syntax to do so:
SELECT *
FROM products
WHERE name ~ '[0-9]+';
id | name | date_sold
----+---------+------------
1 | A0045BH | 2024-09-11
2 | A00933 | 2024-09-12
4 | BR3350 | 2024-09-15
6 | C743559 | 2024-09-22
Notice that the resulting table contains only the rows where the value in the name column contains a number.
Feel free to use similar syntax to select rows that contain a number 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
