Re: Querying for strings that match after prefix

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:%'