pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedWildcard LIKE and Sub-select
3+ messages / 2 participants
[nested] [flat]
* Wildcard LIKE and Sub-select
@ 2006-11-10 15:23 Travis Whitton <tinymountain@gmail.com>
2006-11-10 16:01 ` Re: Wildcard LIKE and Sub-select Erik Jones <erik@myemma.com>
0 siblings, 1 reply; 3+ messages in thread
From: Travis Whitton @ 2006-11-10 15:23 UTC (permalink / raw)
To: pgsql-sql
Hi everybody,
I have two tables of the following structure:
Table "keywords"
column | type
---------------------
id | integer
keyword | varchar(255)
and
Table "badwords"
column | type
----------------------
badword | varchar(255)
I need to delete all the rows from the keywords table where badword
partially matches the keyword field. I know I can do an exact match with a
sub-select, but I'm not sure how to structure a wildcard match / like clause
with a sub-select. Also, is that the best way to do it, or should I be
looking into full-text? I have roughly 10 million keywords and 1 million
badwords.
Thanks,
Travis
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Wildcard LIKE and Sub-select
2006-11-10 15:23 Wildcard LIKE and Sub-select Travis Whitton <tinymountain@gmail.com>
@ 2006-11-10 16:01 ` Erik Jones <erik@myemma.com>
2006-11-10 16:07 ` Re: Wildcard LIKE and Sub-select Travis Whitton <tinymountain@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Erik Jones @ 2006-11-10 16:01 UTC (permalink / raw)
To: Travis Whitton <tinymountain@gmail.com>; +Cc: pgsql-sql
Travis Whitton wrote:
> Hi everybody,
> I have two tables of the following structure:
>
> Table "keywords"
>
> column | type
> ---------------------
> id | integer
> keyword | varchar(255)
>
> and
>
> Table "badwords"
>
> column | type
> ----------------------
> badword | varchar(255)
>
>
> I need to delete all the rows from the keywords table where badword
> partially matches the keyword field. I know I can do an exact match
> with a sub-select, but I'm not sure how to structure a wildcard match
> / like clause with a sub-select. Also, is that the best way to do it,
> or should I be looking into full-text? I have roughly 10 million
> keywords and 1 million badwords.
>
> Thanks,
> Travis
>
Hmm... Maybe (this is untested):
DELETE FROM keywords
USING badwords
WHERE keyword ILIKE ANY (SELECT '%' || badword || '%'
FROM badwords)
--
erik jones <erik@myemma.com>
software development
emma(r)
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Wildcard LIKE and Sub-select
2006-11-10 15:23 Wildcard LIKE and Sub-select Travis Whitton <tinymountain@gmail.com>
2006-11-10 16:01 ` Re: Wildcard LIKE and Sub-select Erik Jones <erik@myemma.com>
@ 2006-11-10 16:07 ` Travis Whitton <tinymountain@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Travis Whitton @ 2006-11-10 16:07 UTC (permalink / raw)
To: Erik Jones <erik@myemma.com>; +Cc: pgsql-sql
I took off the USING clause like so, and it worked like a charm!
DELETE FROM keywords
WHERE keyword ILIKE ANY (SELECT '%' || badword || '%'
FROM badwords)
Thanks so much,
Travis
On 11/10/06, Erik Jones <erik@myemma.com> wrote:
>
> Travis Whitton wrote:
> > Hi everybody,
> > I have two tables of the following structure:
> >
> > Table "keywords"
> >
> > column | type
> > ---------------------
> > id | integer
> > keyword | varchar(255)
> >
> > and
> >
> > Table "badwords"
> >
> > column | type
> > ----------------------
> > badword | varchar(255)
> >
> >
> > I need to delete all the rows from the keywords table where badword
> > partially matches the keyword field. I know I can do an exact match
> > with a sub-select, but I'm not sure how to structure a wildcard match
> > / like clause with a sub-select. Also, is that the best way to do it,
> > or should I be looking into full-text? I have roughly 10 million
> > keywords and 1 million badwords.
> >
> > Thanks,
> > Travis
> >
> Hmm... Maybe (this is untested):
>
> DELETE FROM keywords
> USING badwords
> WHERE keyword ILIKE ANY (SELECT '%' || badword || '%'
> FROM badwords)
>
> --
> erik jones <erik@myemma.com>
> software development
> emma(r)
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-11-10 16:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-10 15:23 Wildcard LIKE and Sub-select Travis Whitton <tinymountain@gmail.com>
2006-11-10 16:01 ` Erik Jones <erik@myemma.com>
2006-11-10 16:07 ` Travis Whitton <tinymountain@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox