IntermediateString Functions

Extract numbers from alphanumeric strings

The query

SQL

SELECT
  name,
  REGEXP_REPLACE(name, '[^0-9]', '', 'g') AS numbers_only,
  REGEXP_REPLACE(name, '[^a-zA-Z]', '', 'g') AS letters_only,
  ARRAY_TO_STRING(
    REGEXP_MATCHES(name, '\d+', 'g')::TEXT[], ','
  ) AS all_numbers
FROM products;

Tested against PostgreSQL 16

Note

“REGEXP_REPLACE with 'g' flag replaces all matches. [^0-9] means "not a digit". Useful for data cleaning.

Tables referenced

products6 cols