LIKE operator two columns clickhouse

Question

I want to select rows in clickhouse table where two string columns are LIKE each other (foe example where column1 is 'Hello' and column2 is '%llo')

I tried LIKE operator:

SELECT * FROM table_name WHERE column1 LIKE column2;

but it said:

Received exception from server (version 21.2.8):
Code: 44. DB::Exception: Received from localhost:9000. DB::Exception: Argument at index 1 for function like must be constant: while executing 'FUNCTION like(column1 : 17, column2 : 17) -> like(column1, column2) UInt8 : 28'. 

it seems that the second argument should be a constant value. Is there any other way to apply this condition?

Answer 1

CH Like supports only constant argument. There is no general solution. The same problem with regex functions and so on. (because Clickhouse applies compiled expression and applies to a column byte-stream before separating to rows).

In some cases you can use position or countSubstrings functions for this task.

Answer 2

You can use LOCATE or POSITION for this (https://clickhouse.tech/docs/en/sql-reference/functions/string-search-functions/). The query would look something like this:

SELECT *
FROM table_name
WHERE position(column1, column2, character_length(column1) - character_length(column2) + 1) > 0;

This may be flawed. It seems that in clickhouse most string functions work on bytes or variable UTF8 byte lengths rather than on characters. One has to pay attentention hence how the functions work and how they should be combined. I am using the third parameter start_pos above and assume that it refers to the character position, but well, it can be bytes just as well - I have not been able to find this information in the docs .