Обсуждение: Substring question

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;

Is it possible to do this in SQL?

Thanks for any help

adam


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

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/

Michael Fuhr <mike@fuhr.org> writes:
> On Mon, Dec 13, 2004 at 06:17:27PM +0000, Adam Witney wrote:
>> I would like to be able to select like this (this is a regular expression I
>> would do in perl)

>   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.

> Is that what you're after?

Also, our regular expression engine is based on Tcl's, which has some
subtle differences from Perl's.  I believe this particular regexp
would act the same in both, but if you are a regexp guru you might
run into things that act differently.

            regards, tom lane