[Solved] How to select the first number in a string

by


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:

SELECT string, 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

3

solved How to select the first number in a string