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: [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 > > > > 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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
