On Thu, Nov 14, 2013 at 2:37 AM, Janek Sendrowski <jane...@web.de> wrote:
> Hi,
> I'm using the pg_trgm module,
> Is there a possibility not to convert one side of the string when comparing?
> I need a kind of reference string containing only certain trigrams.
> It's not possible to build every kind of string using the function.
>
> example:
> Code: Alles auswählen
> SELECT show_trgm('abc');
> show_trgm
> -------------------------
> {" a"," ab",abc,"bc "}
> (1 row)
>
> But I like to have only {'abc'} for example.
>
> I would use this function: "similarity(text, text)"
> Could it somehow work with explicit data-type definitions or maybe with
> putting the string in brackets or quoting?
> Or do I have to change the source code?
>
I guess you're looking for simple pattern matching; something like:
column LIKE '%abc%'
postgres=# create table foo(a text);
CREATE TABLE
postgres=# insert into foo values ('ab'), ('abc'), ('gabcd'), ('xabf');
INSERT 0 4
-- similarity() threshold
postgres=# select show_limit();
show_limit
------------
0.3
(1 row)
-- '%' is similarity operator which returns true if column value is
"sufficiently similar" to key (in this case 'abc'). This is determined
by the number of tri-grams two strings share.
postgres=# select *,similarity(a, 'abc') from foo where a % 'abc';
a | similarity
-----+------------
ab | 0.4
abc | 1
(2 rows)
-- And finally, probably what you're looking for. Simple pattern matching.
postgres=# select * from foo where a LIKE '%abc%';
a
-------
abc
gabcd
(2 rows)
-- You could go ahead and add more trigrams that you'd want result to contain.
postgres=# insert into foo values ('gabcddfg');
INSERT 0 4
postgres=# select * from foo where a LIKE '%abc%dfg%';
a
----------
gabcddfg
(1 row)
Is this what you want?
--
Amit
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
