Re: Querying for strings that match after prefix

# 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