时间戳

首页 > 解决方案 > LIKE 运算符两列 clickhouse

sql - LIKE 运算符两列 clickhouse

问题描述

我想在 clickhouse 表中选择两个字符串列彼此相似的行（foe example where column1 is 'Hello' and column2 is '%llo'）

我试过 LIKE 运算符：

SELECT * FROM table_name WHERE column1 LIKE column2;

但它说：

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'.

似乎第二个参数应该是一个常数值。有没有其他方法可以应用这个条件？

标签： sqlwhere-clausesql-likeclickhouse

解决方案

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 .

推荐阅读