select all matches for a regular expression ?

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}