Re: 9.5.3: substring: regex greedy operator not picking up chars as expected

"Foster, Russell" <Russell.Foster@crl.com> writes:
> For the following query:
> select substring('>772' from '.*?[0-9]+')
> I would expect the output to be '>772', but it is '>7'.

As David pointed out, that's what you get because the RE as a whole is
considered to be non-greedy, ie you get the shortest overall match.
However, you can adjust that by decorating the RE:

# select substring('>772' from '(.*?[0-9]+){1,1}');
 substring
-----------
 >772
(1 row)

Now it's longest-overall, but the .*? part is still shortest-match,
so it doesn't consume any digits.  However, I suspect that still is
not quite what you want, because it consumes too much in cases like:

# select substring('>772foo444' from '(.*?[0-9]+){1,1}');
 substring
------------
 >772foo444
(1 row)

There's probably really no way out of that except to be less lazy about
writing the pattern:

# select substring('>772foo444' from '([^0-9]*?[0-9]+){1,1}');
 substring
-----------
 >772
(1 row)

and in that formulation, of course, greediness doesn't really matter
because there is only one way to match.

# select substring('>772foo444' from '[^0-9]*[0-9]+');
 substring
-----------
 >772
(1 row)

See
https://www.postgresql.org/docs/9.5/static/functions-matching.html#POSIX-MATCHING-RULES

            regards, tom lane