Skip site navigation (1) Skip section navigation (2)

Re: pgsql-sql-digest V1 #499

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 (view raw or flat)
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.

pgsql-sql by date

Next:From: Kovacs Zoltan SandorDate: 2000-02-16 13:00:02
Subject: Re: [SQL] How to set up a simple relational database
Previous:From: Robin KeechDate: 2000-02-16 11:08:28
Subject: Auto increment field when updating?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group