pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.



Message-ID: <200002161255.HAA20544@homeworld.bigpanda.org>
Permalink:  ../200002161255.HAA20544@homeworld.bigpanda.org/
Also on:    postgresql.org/message-id/200002161255.HAA20544@homeworld.bigpanda.org

 · 

reply

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