How to perform a full-text phrase search in ClickHouse?

Shiv Iyer
Posted on April 6, 2023

To perform full-text phrase search in ClickHouse, you can use the match() function in combination with regular expressions. Although ClickHouse does not have a built-in full-text search feature like some other databases, the match() function allows you to perform basic full-text search operations.

Here’s a simple example of how to perform a full-text phrase search using the match() function:

  1. First, let’s create a sample table:

CREATE TABLE documents
id UInt32,
content String
) ENGINE = MergeTree()

2. Insert some sample data into the table:

INSERT INTO documents (id, content)
(1, ‘The quick brown fox jumps over the lazy dog.’),
(2, ‘A journey of a thousand miles begins with a single step.’),
(3, ‘The only way to do great work is to love what you do.’);

3. Perform a full-text phrase search for the phrase “thousand miles”:

SELECT id, content
FROM documents
WHERE match(content, ‘thousand\\s+miles’);

In this query, we use the match() function to search for the phrase “thousand miles” in the content column. The regular expression ‘thousand\\s+miles’ searches for the word “thousand” followed by one or more whitespace characters (\s+) and then the word “miles”. The double backslashes (\) are used to escape the backslash character in the regular expression.

This query will return the following result:

┌─id─ ┬─content────────────────────────┐
│ 2. │ A journey of a thousand miles begins with a single step. └────┴─────────────────────────. ──┘

Keep in mind that this method of full-text phrase search is relatively simple and might not be suitable for large-scale or complex use cases. For advanced full-text search capabilities, you can consider integrating ClickHouse with an external search engine like Elasticsearch.