Re: pg_trgm module: no convertion into Trigrams on one side when comparing

On Thu, Nov 14, 2013 at 2:37 AM, Janek Sendrowski <janek12(at)web(dot)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