Thread: 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/