with test_data as (
  select 'abc 123' as txt
  union
  select 'abc 456' as txt
  union
  select 'blah' as txt
)

select
  txt,
  (regexp_matches(txt, 'd+'))[1] as first_num
from
  test_data

with test_data as (
  select 'abc 123' as txt
  union
  select 'abc 456' as txt
  union
  select 'blah' as txt
)

select
  txt,
  (select regexp_matches(txt, 'd+'))[1] as first_num
from
  test_data

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

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;