Search query for a part of a word
Hello everyone, such a question, there is a servlet in it such a search query:
String query = "select * from clippersql.skisql where kiz='" + kiz + "'";
The problem is that the search occurs only by the full name of the field kiz, how do I make it so that it would search for, say, one letter or part of the field kiz?
2 answers
There are two solutions - the LIKE operator and full-text search.
The first option is simple. To find all records where the kiz field starts with the data in the {[1] variable]}:
String query = "select * from clippersql.skisql where kiz like '" + kiz + "%'";
Setting up the second one is a non - trivial task, the description of which goes beyond the Stackoverflow format.
It is also possible to use a search based on trigrams. pg_trgm
-- создание индекса
CREATE INDEX trgm_idx ON words USING gist (word gist_trgm_ops);
-- пример самого поиска
SELECT word, similarity(word, 'собака') AS sml FROM words
WHERE word % 'собака'
ORDER BY sml DESC, word;
select word from words order by w <-> 'risource' limit 5;
-- pg_trgm supports indexing of regular-expression searches
select w from words where w ~ '^r.*sour.*e$';
Creating such an index optimizes the search for LIKE. that is, if you use {[5] in the app]}
where kiz like '%текст%'
That is, an index with trigrams is used to speed up such a search.