Thread: 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