Thread: Wildcard LIKE and Sub-select

Hi everybody,<br />I have two tables of the following structure:<br /><br />Table "keywords"<br /><br />column   |
type<br/>---------------------<br />id           | integer<br />keyword | varchar(255)<br /><br />and<br /><br />Table
"badwords"<br /><br />column   | type<br />----------------------<br />badword  | varchar(255)<br /><br /><br />I need
todelete all the rows from the keywords table where badword partially matches the keyword field. I know I can do an
exactmatch with a sub-select, but I'm not sure how to structure a wildcard match / like clause with a sub-select. Also,
isthat the best way to do it, or should I be looking into full-text? I have roughly 10 million keywords and 1 million
badwords.<br /><br />Thanks,<br />Travis<br /><br /> 

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)

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