badlydrawnbhoy <badlydrawnbhoy@xxxxxxxxx> wrote: > 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@xxxxxxxxxx should match > mailto:john.smith@xxxxxxxxxx > > 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 There's no need to use a sub-select for this, this should do the job: SELECT REPLACE(address, 'mailto:', '') FROM people; You also have some options for "fuzzy" matching in the WHERE clause, e.g. SELECT address FROM people WHERE address LIKE '%doom@%' Will find all email addresses like "doom@...", whether or not there's a 'mailto:' prefix. (% matches any character string). This will find all the records with the erroneous "mailto:"; prefix: SELECT address FROM people WHERE address LIKE 'mailto:%'
