From: sszabo@bigpanda.com
To: carl@planetcpub.com
Cc: pgsql-sql@hub.org
Subject: Re: pgsql-sql-digest V1 #499
Date: Wed, 16 Feb 2000 07:55:22 -0500
Message-ID: <200002161255.HAA20544@homeworld.bigpanda.org> (raw)
In-Reply-To: <200002160400.XAA49319@hub.org>
>Date: Tue, 15 Feb 2000 09:08:46 -0800
>From: "Carl Flansbaum" <carl@planetcpub.com>
>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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: sszabo@bigpanda.com, carl@planetcpub.com, pgsql-sql@hub.org
Subject: Re: pgsql-sql-digest V1 #499
In-Reply-To: <200002161255.HAA20544@homeworld.bigpanda.org>
References: <200002160400.XAA49319@hub.org> <200002161255.HAA20544@homeworld.bigpanda.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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