Received: from as.tksoft.com (gyw.com [209.55.67.177] (may be forged)) by hub.org (8.10.1/8.11.0) with ESMTP id e9OAkIN20038 for ; Tue, 24 Oct 2000 06:46:18 -0400 (EDT) (envelope-from tjk@tksoft.com) Received: from uno.tksoft.com (smtp3.tksoft.com [192.168.50.56] (may be forged)) by as.tksoft.com (8.8.8/8.8.8) with ESMTP id DAA31403; Tue, 24 Oct 2000 03:51:32 -0700 Received: (from tjk@tksoft.com) by uno.tksoft.com (8.8.8/8.8.8) id DAA13735; Tue, 24 Oct 2000 03:47:28 -0700 From: "tjk@tksoft.com" Message-Id: <200010241047.DAA13735@uno.tksoft.com> Subject: Re: Help: Using a regular expression match as a value To: radevenz@ix.netcom.com (Richard DeVenezia) Date: Tue, 24 Oct 2000 03:47:26 -0700 (PDT) Cc: pgsql-sql@postgresql.org In-Reply-To: <023401c03b69$bcc55df0$0101a8c0@supermicro> from "Richard DeVenezia" at Oct 21, 2000 10:18:07 AM X-Mailer: ELM [version 2.5 PL2] MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200010/204 X-Sequence-Number: 1866 Richard, Please correct me if I am wrong. You want to do something like: select $1 from captives where firstname ~ '^(R[^ \t,]*d)$'; And get as result: lastname Richard Richard Richard Ricard Richard Rolland Richard In this case, the above query is same as: select firstname from captives where firstname ~ '^R[^ \t,]*d$'; If the firstname field contains entries like "Richard, Elvis" and "Richard Amadeus," then you want something like: select $1 from captives where firstname ~ '^(R[^ \t,]*d)[ \t,]'; which would return the first name in the field but not subsequent names. I am afraid there is no built in solution to get around these scenarios. You could do: select substring(firstname from 1 for (position(' ' in firstname)-1)) from captives where firstname ~ '^R[^ ]*d ' union select firstname from captives where firstname ~ '^R[^ ]*d$'; This would get you what you wanted, for this one scenario. I know it's not what you hoped, but you might be able to make do with this. Troy > > I am just getting started with pgsql and have read available docs I can > find. > > I know I can match a row in a where clause using a regular expression. > > How can I use what was regexp matched (e.g. perl $1,$2, etc...) as a column > assignment ? > > I'm looking for something like this? > select ... > ... > $1 of firstname, /^R.*d/ as name_starts_with_r_and_ends_with_d > ... > > TIA > > Richard DeVenezia > >