Clickhouse how to filter string by control characters

user_519951 0
Viewed 593

In Clickhouse, how do I filter strings by control characters e.g. tab \t, newline \n

SQL Server has CHAR function to express control chars. Separately, Hive has rlike for regex expressions that can match control chars. How do you do something similar in CH?

I do not know how to escape the tab character properly in the following commands. No matter the number of backslash 1, 2 or 4:

$ clickhouse-client --query="SELECT 'Hello\tworld'"
Hello\tworld

$ clickhouse-client --query="SELECT 'Hello\\tworld'"
Hello\tworld

$ clickhouse-client --query="SELECT 'Hello\\\\tworld'"
Hello\\tworld

Clickhouse version 21.3.11.1

clickhouse
1 Answers

You see the output in TSV format, so \t converted twice \t -> 0x9 -> \t

clickhouse-client --query="SELECT 'Hello\tworld' col format PrettyCompact"
┌─col─────────┐
│ Hello world │
└─────────────┘

You can use \t \n \r and char(9), char(10), char(13)

clickhouse-client --query="SELECT 'Hello\tworld' like '%'||char(9)||'%' res format PrettyCompact"
┌─res─┐
│   1 │
└─────┘

clickhouse-client --query="SELECT 'Hello\tworld' like '%\t%' res format PrettyCompact"
┌─res─┐
│   1 │
└─────┘
user_116443080
Related