Обсуждение: Querying for strings that match after prefix
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
On Fri, Jun 02, 2006 at 02:47:22AM -0700, badlydrawnbhoy wrote: > 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. Look here: http://www.postgresql.org/docs/7.4/interactive/functions-string.html especially the substring function will help you. Joachim PS: You should also consider upgrading to 7.4.13
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@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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
Or something like select ltrim(substr(address, 8)) from people where address like 'mailto:%' union select address from people where address not like 'mailto:%' John 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@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 >> >> >> ---------------------------(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@postgresql.org so that your > message can get through to the mailing list cleanly
John Sidney-Woollett wrote: >>> 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. > > Or something like > > select ltrim(substr(address, 8)) from people where address like > 'mailto:%' union select address from people where address not like > 'mailto:%' > Could you explain why the UNION? brian
1) select ltrim(substr(address, 8)) from people where address like 'mailto:%' gives all addresses that start with "mailto:" but first strips off the prefix leaving only the email address 2) select address from people where address not like 'mailto:%' produces all email address that don't need the prefix stripped off The UNION of the two gives you all the unique/distinct addresses by combining the results from the first and second query. John brian ally wrote: > John Sidney-Woollett wrote: > >>>> 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. > > > > >> Or something like >> >> select ltrim(substr(address, 8)) from people where address like >> 'mailto:%' union select address from people where address not like >> 'mailto:%' >> > > Could you explain why the UNION? > > brian > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend
John Sidney-Woollett wrote: > brian ally wrote: > >> John Sidney-Woollett wrote: >> >>>>> 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. >>> >>> Or something like >>> >>> select ltrim(substr(address, 8)) from people where address like >>> 'mailto:%' union select address from people where address not >>> like 'mailto:%' >>> >> >> Could you explain why the UNION? >> >> brian > 1) select ltrim(substr(address, 8)) from people where address like > 'mailto:%' > > gives all addresses that start with "mailto:" but first strips off > the prefix leaving only the email address > > 2) select address from people where address not like 'mailto:%' > > produces all email address that don't need the prefix stripped off > > The UNION of the two gives you all the unique/distinct addresses by > combining the results from the first and second query. Right, of course. I'd forgotten that the original query was not simply to select the bad addresses, but to grab them all. Thanks for the clarification. brian
badlydrawnbhoy <badlydrawnbhoy@gmail.com> 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@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 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:%'
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@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 > > > > > > ---------------------------(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@postgresql.org so that your > message can get through to the mailing list cleanly
# badlydrawnbhoy@gmail.com / 2006-06-02 05:18:08 -0700:
> 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!
Not unclear, this question is a completely different animal.
Pick one:
SELECT p.*
FROM people p,
(SELECT REPLACE(address, 'mailto:', '') AS stripped
FROM people
WHERE address LIKE 'mailto:%') AS m
WHERE p.address = m.stripped;
SELECT *
FROM people p
WHERE p.address IN (
SELECT REPLACE(address, 'mailto:', '') AS stripped
FROM people
WHERE address LIKE 'mailto:%');
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991