Re: Querying for strings that match after prefix

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.smith@smiths.com should match
> > mailto:john.smith@smiths.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@postgresql.org so that your
>        message can get through to the mailing list cleanly