Re: pgsql-sql-digest V1 #499

>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.