Geek Answers Handbook

How to SQL query for punctuation words in Postgresql?

If I have lines / phrases like this stored in the database:

  • What are Q-type operations?
  • Programmer's Guide
  • ABC coding

Is there a way to pass the query parameter in the form of "Programmers"or "abc"or "q-type"and find it "Programmer's", "A.B.C"and "q-type"?

+5
source share
6 answers

search vector

Use a type tsvectorthat is part of the PostgreSQL text search function.

postgres> select 'What are Q-type Operations?'::tsvector;
              tsvector               
-------------------------------------
 'Operations?' 'Q-type' 'What' 'are'
(1 row)

You can also use familiar tsvectors operators:

postgres> select 'What are Q-type Operations?'::tsvector
postgres>        || 'A.B.C' of Coding'::tsvector;
                           ?column?                           
--------------------------------------------------------------
 'A.B.C''s' 'Coding' 'Operations?' 'Q-type' 'What' 'are' 'of'

From the tsvector documentation:

tsvector , ( . 12).

, , ( "the", "a" ..) , to_tsvector. :

postgres> select to_tsvector('english',
postgres> 'What are Q-type Operations? A.B.C' of Coding');
                      to_tsvector                       
--------------------------------------------------------
 'a.b.c':7 'code':10 'oper':6 'q':4 'q-type':3 'type':5
(1 row)

, - tsvector ts_query() . GiST tsvector.

postgres> insert into text (phrase, tsvec)
postgres>   values('What are Q-type Operations?',
postgres>   to_tsvector('english', 'What are Q-type Operations?'));
INSERT 0 1

tsquery @@:

postgres> select phrase from text where tsvec @@ to_tsquery('q-type');
           phrase            
-----------------------------
 What are Q-type Operations?
(1 row)
+5

ILIKE TRANSLATE, . .

: translate(field, '.-\'', '')

+2

, . , .

SQL Server: ?

+1

, - :

http://www.postgresql.org/docs/9.0/static/fuzzystrmatch.html

100%, , .

EDIT , ( PostgreSQL 9.0 Windows)

:

template1=> select soundex('Programmers'), soundex('Programmer''s');
 soundex | soundex
---------+---------
 P626    | P626
(1 row)


template1=> select soundex('abc'), soundex('A.B.C.');
 soundex | soundex
---------+---------
 A120    | A120
(1 row)


template1=> select soundex('Q-type'), soundex('q-type');
 soundex | soundex
---------+---------
 Q310    | Q310
(1 row)

, soundex(colname) = soundex(<user param>), , where.

fuzzystrmatch:

psql -U <dbowner> -d <database> -f SHAREDIR/contrib/fuzzystrmatch.sql

, SHAREDIR

, , , ts_vector , .

0

Postgresql tsvector:

steve@steve@[local] =# select input, to_tsvector('english', input)\
   from (values('What are Q-type Operations?'),('Programmer' Guide'),('A.B.C' of Coding')) x(input);
            input            |            to_tsvector             
-----------------------------+------------------------------------
 What are Q-type Operations? | 'oper':6 'q':4 'q-type':3 'type':5
 Programmer Guide          | 'guid':3 'programm':1
 A.B.C of Coding           | 'a.b.c':1 'code':4
(3 rows)

, , , "", "" "" , .

Normally you would use this by specifying a column or expression of the indexed tsvector, and then using the operator @@to match the value tsquery, for example:

steve@steve@[local] =# select input, to_tsvector('english', input) \
   from (values('What are Q-type Operations?'),('Programmer' Guide'),('A.B.C' of Coding')) x(input)\
   where to_tsvector('english', input) @@ plainto_tsquery('english', 'programmers');
       input        |      to_tsvector      
--------------------+-----------------------
 Programmer Guide | 'guid':3 'programm':1
(1 row)

Here, it plainto_tsqueryanalyzes the user input line and issues a query in which each non-stop word in the query must correspond to a tsvector.

0
source

More articles:


All Articles