Обсуждение: select all matches for a regular expression ?

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

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.

Peter.

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

"Anton Melser" <melser.anton@gmail.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.

            regards, tom lane

On 23/02/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Anton Melser" <melser.anton@gmail.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

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.an...@gmail.com ("Anton Melser") wrote:
> On 23/02/07, Tom Lane <t...@sss.pgh.pa.us> wrote:
>
> > "Anton Melser" <melser.an...@gmail.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/