badlydrawnbhoy <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] should match > mailto:[EMAIL PROTECTED] > > 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 '[EMAIL PROTECTED]' Will find all email addresses like "[EMAIL PROTECTED]", 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:%' ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
