From: | "badlydrawnbhoy" <badlydrawnbhoy(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Querying for strings that match after prefix |
Date: | 2006-06-02 12:18:08 |
Message-ID: | 1149250688.438282.104260@g10g2000cwb.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi there,
I think I need to explain a bit further.
I tried simply using
update people
replace(address, 'mailto:','');
but unfortunately that produced a duplicate key error as some of the
addresses prefixed with 'mailto:' are already present (unprefixed) in
the table.
So what I need to do is find those entries - those items in the table
for which there is an equivalent entry prefixed with 'mailto:'.
Sorry if I'm not being very clear!
Cheers
BBB
John Sidney-Woollett wrote:
> Do you mean?
>
> select replace(address, 'mailto:', '') from people
>
> ... and if you only want to find the ones that start with "mailto:"
>
> select replace(address, 'mailto:', '') from people
> where address like 'mailto:%'
>
> John
>
> badlydrawnbhoy wrote:
> > Hi all,
> >
> > I hope this is the right forum for this, but please correct me if
> > somewhere else is more appropriate.
> >
> > I need to locate all the entries in a table that match , but only after
> > a number of characters have been ignored. I have a table of email
> > addresses, and someone else has erroneously entered some addresses
> > prefixed with 'mailto:', which I'd like to ignore.
> >
> > An example would be: john(dot)smith(at)smiths(dot)com should match
> > mailto:john(dot)smith(at)smiths(dot)com
> >
> > I've tried the following
> >
> > select address
> > from people
> > where address = (select replace(address, 'mailto:', '') from people);
> >
> > which gives me the error
> >
> > ERROR: more than one row returned by a subquery used as an expression
> >
> > I'm running on PostgreSQL 7.4.7
> >
> > Thanks in advance,
> >
> > BBB
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
From | Date | Subject | |
---|---|---|---|
Next Message | brian ally | 2006-06-02 13:04:36 | Re: Querying for strings that match after prefix |
Previous Message | John Sidney-Woollett | 2006-06-02 12:17:50 | Re: Querying for strings that match after prefix |