How To Select The First Number In A String

Basically I have a big list of addresses that can sometimes start with FLAT 'number', 'rest of address' or APARTMENT 'number', 'rest of address' or sometimes even 'number'A, 'rest

Solution 1:

This substring() expression does what you ask:

substring(string, '\m\d+\D?*\M')

The regular expression only returns the first match, or NULL if none.

\m ... beginning of a word\d+ ... one or more digits \D? ... zero or one non-digits \M ... end of word

Demo:

SELECTstring, substring(string, '\d+\D?\d*\M')FROM  (
   VALUES
  ('FLAT 3, thanos house, nw1 6fs')
, ('FLAT 3B, thanos house, nw1 6fs')
, ('324, thanos house, nw1 6fs')
, ('APARTMENT 324, thanos house, nw1 6fs')
   ) tbl(string);

db<>fiddle here