Querying for strings that match after prefix - Mailing list pgsql-general

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