From: | sszabo(at)bigpanda(dot)com |
---|---|
To: | carl(at)planetcpub(dot)com |
Cc: | pgsql-sql(at)hub(dot)org |
Subject: | Re: pgsql-sql-digest V1 #499 |
Date: | 2000-02-16 12:55:22 |
Message-ID: | 200002161255.HAA20544@homeworld.bigpanda.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
>Date: Tue, 15 Feb 2000 09:08:46 -0800
>From: "Carl Flansbaum" <carl(at)planetcpub(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Kovacs Zoltan Sandor | 2000-02-16 13:00:02 | Re: [SQL] How to set up a simple relational database |
Previous Message | Robin Keech | 2000-02-16 11:08:28 | Auto increment field when updating? |