Often in PostgreSQL you may want to extract only numbers from a string.
You can use the following syntax to do so:
SELECT NULLIF(regexp_replace(name, '\D','','g'), '') AS num_only
FROM products;
This particular example will extract only the numbers from each string in the name column of the table named products.
The results will be saved to a new column named num_only.
Note that we use the regex_preplace() function to replace each non-digit ( \D ) in the string with nothing, and we use the g parameter to specify that this replacement should be made globally, i.e. for each occurrence in the string.
We then wrap this function with NULLIF so that if a string has no numbers then the result will simply be blank.
The following example shows how to use this syntax in practice.
Example: Extract Numbers from a String 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, 'B4277JR', '2024-09-10');
INSERT INTO products VALUES (0004, 'BR3350', '2024-09-15');
INSERT INTO products VALUES (0005, 'CHR8873', '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 | B4277JR | 2024-09-10
4 | BR3350 | 2024-09-15
5 | CHR8873 | 2024-09-18
6 | C743559 | 2024-09-22
Suppose that we would like to extract only the numbers from each string in the name column.
We can use the following syntax to do so:
SELECT name,
NULLIF(regexp_replace(name, '\D','','g'), '') AS num_only
FROM products;
name | num_only
---------+----------
A0045BH | 0045
A00933 | 00933
B4277JR | 4277
BR3350 | 3350
CHR8873 | 8873
C743559 | 743559
Notice that the resulting table contains a new column named num_only that contains only the numbers from the original string in the name column.
Feel free to use similar syntax to extract only numbers from your own string field.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Extract Substring Before Specific Character
PostgreSQL: How to Extract Substring Between Two Characters
PostgreSQL: How to Extract Date from a Timestamp
