From: | Roman Neuhauser <neuhauser(at)sigpipe(dot)cz> |
---|---|
To: | badlydrawnbhoy <badlydrawnbhoy(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Querying for strings that match after prefix |
Date: | 2006-06-03 15:03:45 |
Message-ID: | 20060603150345.GA1044@dagan.sigpipe.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
# 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
From | Date | Subject | |
---|---|---|---|
Next Message | Harald Armin Massa | 2006-06-03 16:17:16 | Re: Using postgresql as desktop DB |
Previous Message | Yavuz Kavus | 2006-06-03 14:30:54 | Performance difference between char and int2 columns |