Re: select all matches for a regular expression ?

I'm going to disagree and say it can be done (maybe).

Use regexp_replace() to convert non-numeric characters. Depending on
your final needs, you could leave it as a comma-separated list or
split it to an array.

select string_to_array(regexp_replace(regexp_replace('hello4 is 4 very
n1ce num8er', '[^0-9]+', ',', 'g'), '^,|,$', '', 'g'),',');

{4,4,1,8}

On Feb 23, 10:18 am, melser(dot)an(dot)(dot)(dot)(at)gmail(dot)com ("Anton Melser") wrote:
> On 23/02/07, Tom Lane <t(dot)(dot)(dot)(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> > "Anton Melser" <melser(dot)an(dot)(dot)(dot)(at)gmail(dot)com> writes:
> > > 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?
>
> > You need plperl (or pltcl; likely plpython would work too) --- the
> > built-in regex functions don't have any way to return more than the
> > first match. There's a patch pending to provide more functionality
> > here for 8.3, but it won't help you today.
>
> Thanks for the info
> Cheers
> Anton
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org/