Substring question

Started by Adam Witneyover 21 years ago3 messagesgeneral
Jump to latest
#1Adam Witney
awitney@sghms.ac.uk

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.

#2Michael Fuhr
mike@fuhr.org
In reply to: Adam Witney (#1)
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/

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: Michael Fuhr (#2)
Re: Substring question

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