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@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 > > > > 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@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly
