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 thisRv0001c_f
Rv0002_r
Rv1003c_rEtc
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