Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

WITH test_data(txt) AS (
   VALUES
      (text 'abc 123')
    , ('abc 456')
    , ('blah')
   )
SELECT txt, substring(txt, '\d+') AS first_num
from   test_data;


txt
first_num


abc 123
123

abc 456
456

blah
null



SELECT 3


WITH test_data(txt) AS (
   VALUES
      (text 'abc 123')
    , ('abc 456')
    , ('blah')
   )
SELECT txt, (regexp_match(txt, '\d+'))[1] AS first_num
from   test_data;


txt
first_num


abc 123
123

abc 456
456

blah
null



SELECT 3


-- original problem:
WITH test_data(txt) AS (
   VALUES
      (text 'abc 123')
    , ('abc 456')
    , ('blah')
   )
SELECT txt, (regexp_matches(txt, '\d+'))[1] AS first_num
from   test_data;


txt
first_num


abc 123
123

abc 456
456



SELECT 2