Re: select all matches for a regular expression ?

On 23/02/07, Peter Childs <peterachilds@gmail.com> wrote:
> On 23/02/07, Anton Melser <melser.anton@gmail.com> wrote:
> > Hi,
> > I need to be able to get all the matches for a particular regexp from
> > a text field that I need to use in another query in a function. Is
> > this possible with plpgsql? Do I have to install the perl language?
> > Cheers
> > Anton
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
> yes. use "like" or "~"
>
> see
> http://www.postgresql.org/docs/8.2/static/functions-matching.html
>
> There is no need to use perl.

... I have read and re-read that page many times - I must be stupid
:-(. For me both like and ~ on an expression will return true or
false, and not a set of values. I have managed to get *one* value with
substring(), but I need to get them all...
As an example, I need to find all the occurences of digits in the following text

myvar := 'hello4 is 4 very n1ce num8er';

so select substrings(myvar, '([0-9]));

will return
4
4
1
8

Is *this* possible without perl? Could you give a paragraph number on
that page if the info is there so I know exactly where to look?
Thanks again,
Anton