Postgres 15

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

create table test(sample,comment)as values
  ('bax 45456xx888z 89776uuu','no digits with an `a` up front'),
  ('abax 45456xx888z 89776uuu','`a` before everything'),
  ('bax a45456xxa888z 89776uuu','the first 2 digit sequences have a leading `a`, none up front');
--assuming `a` is undesired right in front of the match, not the whole thing
select * from test
cross join lateral regexp_matches(sample,'((?<!a\d*)\d+)','gi');

SELECT 3




sample
comment
regexp_matches


bax 45456xx888z 89776uuu
no digits with an `a` up front
{45456}

bax 45456xx888z 89776uuu
no digits with an `a` up front
{888}

bax 45456xx888z 89776uuu
no digits with an `a` up front
{89776}

abax 45456xx888z 89776uuu
`a` before everything
{45456}

abax 45456xx888z 89776uuu
`a` before everything
{888}

abax 45456xx888z 89776uuu
`a` before everything
{89776}

bax a45456xxa888z 89776uuu
the first 2 digit sequences have a leading `a`, none up front
{89776}



SELECT 7


--assuming `a` is undesired in front of the whole thing, 
--but allowed right in front of each match
--https://stackoverflow.com/questions/78433558/repeated-matches-with-a-start-anchor-in-regexp-matches?noredirect=1#comment138276296_78433558
select * from test
cross join lateral regexp_matches(sample,'(?<!^a.*)\d+','gi');


sample
comment
regexp_matches


bax 45456xx888z 89776uuu
no digits with an `a` up front
{45456}

bax 45456xx888z 89776uuu
no digits with an `a` up front
{888}

bax 45456xx888z 89776uuu
no digits with an `a` up front
{89776}

bax a45456xxa888z 89776uuu
the first 2 digit sequences have a leading `a`, none up front
{45456}

bax a45456xxa888z 89776uuu
the first 2 digit sequences have a leading `a`, none up front
{888}

bax a45456xxa888z 89776uuu
the first 2 digit sequences have a leading `a`, none up front
{89776}



SELECT 6


--alternative, catching both the undesired and the desired pattern
--then only returning hits where undesired wasn't found
--https://stackoverflow.com/questions/78433558/repeated-matches-with-a-start-anchor-in-regexp-matches?noredirect=1#comment138279840_78433558
select test.*,m[2] from test
inner join lateral regexp_matches(sample,'(^a.*)|(\d+)','gi')m on m[1] is null;


sample
comment
m


bax 45456xx888z 89776uuu
no digits with an `a` up front
45456

bax 45456xx888z 89776uuu
no digits with an `a` up front
888

bax 45456xx888z 89776uuu
no digits with an `a` up front
89776

bax a45456xxa888z 89776uuu
the first 2 digit sequences have a leading `a`, none up front
45456

bax a45456xxa888z 89776uuu
the first 2 digit sequences have a leading `a`, none up front
888

bax a45456xxa888z 89776uuu
the first 2 digit sequences have a leading `a`, none up front
89776



SELECT 6

sample	comment	regexp_matches
bax 45456xx888z 89776uuu	no digits with an `a` up front	{45456}
bax 45456xx888z 89776uuu	no digits with an `a` up front	{888}
bax 45456xx888z 89776uuu	no digits with an `a` up front	{89776}
abax 45456xx888z 89776uuu	`a` before everything	{45456}
abax 45456xx888z 89776uuu	`a` before everything	{888}
abax 45456xx888z 89776uuu	`a` before everything	{89776}
bax a45456xxa888z 89776uuu	the first 2 digit sequences have a leading `a`, none up front	{89776}

sample	comment	m
bax 45456xx888z 89776uuu	no digits with an `a` up front	45456
bax 45456xx888z 89776uuu	no digits with an `a` up front	888
bax 45456xx888z 89776uuu	no digits with an `a` up front	89776
bax a45456xxa888z 89776uuu	the first 2 digit sequences have a leading `a`, none up front	45456
bax a45456xxa888z 89776uuu	the first 2 digit sequences have a leading `a`, none up front	888
bax a45456xxa888z 89776uuu	the first 2 digit sequences have a leading `a`, none up front	89776