Re: Regex substring help

On Thu, Sep 03, 2009 at 03:22:12PM -0700, Nick wrote:
> Im trying to get all the text before the '<br>' tag.
>
> SELECT SUBSTRING('one<br>two<br>three','(^.*)<br>.*$');
>
> returns "one<br>two"
>
> How do I get it to return "one"?

You can either use a non-greedy regex like this:

SELECT substring('one<br>two<br>three','(^.*?)<br>.*$');

Note the '?' after the '*'. That makes it non-greedy.

Another way to do this would be with string_to_array:

SELECT (string_to_array('one<br>two<br>three','<br>'))[1];

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate