pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: tjk@tksoft.com <tjk@tksoft.com>
To: radevenz@ix.netcom.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Help: Using a regular expression match as a value
Date: Tue, 24 Oct 2000 03:47:26 -0700 (PDT)
Message-ID: <200010241047.DAA13735@uno.tksoft.com> (raw)
In-Reply-To: <023401c03b69$bcc55df0$0101a8c0@supermicro>

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




view thread (2+ messages)

Message-ID: <200010241047.DAA13735@uno.tksoft.com>
Permalink:  ../200010241047.DAA13735@uno.tksoft.com/
Also on:    postgresql.org/message-id/200010241047.DAA13735@uno.tksoft.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tjk@tksoft.com, radevenz@ix.netcom.com
  Subject: Re: Help: Using a regular expression match as a value
  In-Reply-To: <200010241047.DAA13735@uno.tksoft.com>
  References: <023401c03b69$bcc55df0$0101a8c0@supermicro> <200010241047.DAA13735@uno.tksoft.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox