Received: from homeworld.bigpanda.org (IDENT:root@client-151-198-27-104.bellatlantic.net [151.198.27.104]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA97500 for ; Wed, 16 Feb 2000 07:55:18 -0500 (EST) (envelope-from acroyear@homeworld.bigpanda.org) Received: from homeworld.bigpanda.org (IDENT:acroyear@localhost.awc.net [127.0.0.1]) by homeworld.bigpanda.org (8.9.3/8.9.3) with ESMTP id HAA20544; Wed, 16 Feb 2000 07:55:22 -0500 Message-Id: <200002161255.HAA20544@homeworld.bigpanda.org> To: carl@planetcpub.com CC: pgsql-sql@hub.org From: sszabo@bigpanda.com Subject: Re: pgsql-sql-digest V1 #499 In-Reply-To: Your message of "Tue, 15 Feb 2000 23:00:18 EST." <200002160400.XAA49319@hub.org> Date: Wed, 16 Feb 2000 07:55:22 -0500 Sender: acroyear@bigpanda.com >Date: Tue, 15 Feb 2000 09:08:46 -0800 >From: "Carl Flansbaum" >Subject: Techniques for quickly finding words in a phrase... > >Hello, >Ok, can anyone help with the following... >I'm also looking to write a SQL query to find a specific string in a target >of words. >the field contains values like 1234 2456 1234a etc. >The problem is that the string must be exact, so while using >WHERE foo ~ '1234'; >WHERE foo LIKE '1234'; >WHERE foo ~* '[[:<:]]1234'; >will work, it also brings up results with the 1234a value in the field. > >I've tried a wide variety of pattern matching with no luck. Well, you'll probably be better off looking at the fulltextindex stuff under contrib in any case, because any non-anchored regexp over a big table is likely to be painful. Basically I believe the idea is use an trigger to break the words in the row into a new table so you can use equality or anchored regexps to get the values. However, in reasonably current sources, WHERE foo ~* '( |^)1234( |$)' seems to get the correct results.