pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp: Using a regular expression match as a value
2+ messages / 2 participants
[nested] [flat]
* Help: Using a regular expression match as a value
@ 2000-10-21 14:18 Richard DeVenezia <radevenz@ix.netcom.com>
2000-10-24 10:47 ` Re: Help: Using a regular expression match as a value tjk@tksoft.com <tjk@tksoft.com>
0 siblings, 1 reply; 2+ messages in thread
From: Richard DeVenezia @ 2000-10-21 14:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help: Using a regular expression match as a value
2000-10-21 14:18 Help: Using a regular expression match as a value Richard DeVenezia <radevenz@ix.netcom.com>
@ 2000-10-24 10:47 ` tjk@tksoft.com <tjk@tksoft.com>
0 siblings, 0 replies; 2+ messages in thread
From: tjk@tksoft.com @ 2000-10-24 10:47 UTC (permalink / raw)
To: radevenz@ix.netcom.com; +Cc: pgsql-sql
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
>
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-10-24 10:47 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-10-21 14:18 Help: Using a regular expression match as a value Richard DeVenezia <radevenz@ix.netcom.com>
2000-10-24 10:47 ` tjk@tksoft.com <tjk@tksoft.com>
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