Posix regex in Postgresql to extract from quoted text?

Question

I'm trying to extract a number of strings from column values in PostgreSQL.

So basically I have text in columns in a table that look something like:

blah blah blah.... <something="AValueIWant">....dfdf.gd d.fg d.fd... <something="AnotherValueIWant">.

I want to be able to run a query to extract "AValueIWant" and "AnotherValueIWant" (without the quotes) in a form like this:

regex_matches
-------------
AValueIWant
AnotherValueIWant

Let's call the column "body" and the table "tablebody".

So far I have something like:

select regexp_matches(body, <some kind of pattern that doesn't work>, 'g') from tablebody

I don't feel there is any value posting up what I've tried since they all don't work! I've looked at the docos and tried things like 'something(.+)\"' but that returned the whole body text. Other patterns I've found online like '/"([^"]*/"' return nothing at all. Does anyone else have a better grasp of regex that knows the answer, or another way to do this :)? I'm pretty newbie.

Thanks!

Is the "something" key always the same?

Loamhoof
– Loamhoof

2013-04-08 09:33:36 +00:00
Commented Apr 8, 2013 at 9:33 — Loamhoof
– Loamhoof, Commented Apr 8, 2013 at 9:33

Clodoaldo Neto · Accepted Answer · 2013-04-08 11:09:21Z

2

select a[1]
from (
    select regexp_matches(body, '="(.+?)"', 'g') a
    from tablebody
) s

But I would rather use a specialized HTML parser unless it is a very quick and dirt job you want.

edited Apr 8, 2013 at 11:09

answered Apr 8, 2013 at 9:50

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zakinster · Accepted Answer · 2013-04-08 09:34:55Z

0

You can try this :

<something="([^"]+)">

Or more generic :

<[A-Za-z]+="([^"]+)">

Or even more generic, if you don't care about the name of the attribute:

="([^"]+)"

Or...well you got the idea.

answered Apr 8, 2013 at 9:34

zakinster

10.7k1 gold badge45 silver badges54 bronze badges

Collectives™ on Stack Overflow

Posix regex in Postgresql to extract from quoted text?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related