Re: Querying for strings that match after prefix

# badlydrawnbhoy(at)gmail(dot)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