Received: from antares.mcs.anl.gov (mcs.anl.gov [140.221.9.6]) by hub.org (8.8.8/8.8.8) with SMTP id KAA23468 for ; Mon, 19 Oct 1998 10:32:22 -0400 (EDT) (envelope-from selkovjr@mcs.anl.gov) Received: from mcs.anl.gov (selkovjr@slip55.slip.anl.gov [146.137.252.104]) by antares.mcs.anl.gov (8.6.10/8.6.10) with ESMTP id JAA07847; Mon, 19 Oct 1998 09:32:16 -0500 Message-Id: <199810191432.JAA07847@antares.mcs.anl.gov> To: Tony.Curtis@vcpc.univie.ac.at Cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] [SQL, regex, words] how to match word boundaries using regex's? In-Reply-To: Your message of "Mon, 19 Oct 1998 15:02:32 +0200." <199810191302.PAA05430@haze.vcpc.univie.ac.at> Date: Mon, 19 Oct 1998 09:33:23 -0400 From: "Gene Selkov Jr." > I want to do a regex match limited to words. > > I tried this: > > where ... ~ '\Wword\W'; > where ... ~ '\W*word\W*'; > where ... ~ '\b\(word\)\b'; > > and other things with LIKE but no joy. Based on the comments in the source, regexp stuff used in postgres is something like this: http://tiger8.com/us/regexp-manpage.html I guess there are no backslash macros is POSIX expressions. No joy. By the way, I am wondering what determined the choice of the regexp machine for postgres? Is it performance-related? Is it possible to have the same stuff as in perl? As to your question, how about a poor man's Altavista like this: Split the text into words before loading into a special index table. Words are numbered sequentially, so you can search for "phrases": Table = word +----------------------------------+----------------------------------+-------+ | Field | Type | Length| +----------------------------------+----------------------------------+-------+ | rec | char() | 12 | | seq | int4 | 4 | | word | text | var | +----------------------------------+----------------------------------+-------+ SELECT DISTINCT w1.rec FROM word w1, word w2 WHERE w1.word ~ '^a$' AND w2.word ~ '^phrase$' AND w1.rec = w2.rec AND w2.seq - w1.seq = 1; -- Distance between the words This way, you can control what represents the concept of a 'word' by an external program (perl script, etc.) Certainly, this method will show suboptimal performance with extra large tables and more than three or four words in a seach phrase. But it is possible to optimise by delegating set operations (joins) and position arithmetic to the client. It works very well for my ~500k tables and the most common queries. --Gene