Querying for strings that match after prefix

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:

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:

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:

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:

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

# 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