Re: Substring question

On Mon, Dec 13, 2004 at 06:17:27PM +0000, Adam Witney wrote:
>
> I am trying to select a part of a text field based on a regular expression,
> the data looks like this
>
> Rv0001c_f
> Rv0002_r
> Rv1003c_r
>
> Etc
>
> I would like to be able to select like this (this is a regular expression I
> would do in perl)
>
> SELECT substring(primer_name, '(\w+)\d\d\d\d[c]*_[fr]$') from primer;

The "POSIX Regular Expressions" section in the manual contains the
following note:

  Remember that the backslash (\) already has a special meaning in
  PostgreSQL string literals.  To write a pattern constant that
  contains a backslash, you must write two backslashes in the
  statement.

SELECT substring(primer_name, '(\\w+)\\d\\d\\d\\d[c]*_[fr]$') FROM primer;
 substring
-----------
 Rv
 Rv
 Rv
(3 rows)

Is that what you're after?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/