Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 50F2D9FA3A7 for ; Fri, 10 Nov 2006 11:23:56 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 17193-03 for ; Fri, 10 Nov 2006 11:23:45 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.234]) by postgresql.org (Postfix) with ESMTP id 44C7B9F9B3F for ; Fri, 10 Nov 2006 11:23:45 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so499533wxd for ; Fri, 10 Nov 2006 07:23:43 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:mime-version:content-type; b=iRl7uFD1APGoqbLaL3N9dJP6jsU50IPiDpK+fEElTFcUuZB7LoKRZJmm6U0gp2FgbORufGj4kuDAOPtMUMUv9QJX40WCo6iz9wlXPcgK6K8LqUutlcfHsj/bfWqps3OT4rwoMHTyqtRn6I2DAfZ5ndoNCyCa+MiKqCW0gkuiBqE= Received: by 10.70.90.17 with SMTP id n17mr3248286wxb.1163172213756; Fri, 10 Nov 2006 07:23:33 -0800 (PST) Received: by 10.70.72.14 with HTTP; Fri, 10 Nov 2006 07:23:33 -0800 (PST) Message-ID: Date: Fri, 10 Nov 2006 10:23:33 -0500 From: "Travis Whitton" To: pgsql-sql@postgresql.org Subject: Wildcard LIKE and Sub-select MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_46340_2881872.1163172213725" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/111 X-Sequence-Number: 26763 ------=_Part_46340_2881872.1163172213725 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_46340_2881872.1163172213725 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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

------=_Part_46340_2881872.1163172213725--