Clickhouse query use regular expression

Question

I know clickhouse provides replaceRegexpOne() function, but I want to use a regular expression to query, not replace. Like MySQL:

select username 
from table 
where username  REGEXP '^[0-9]*$'.

Hope you can help me, Thanks.

Answer 1

Since 2023 the OP example works as well: username REGEXP '^[0-9]*$' is valid clickhouse sql.

There's match(haystack, pattern) function

Checks whether the string matches the pattern regular expression. A re2 regular expression. The syntax of the re2 regular expressions is more limited than the syntax of the Perl regular expressions.

Use as:

WHERE match(column, 'pattern')
-- or
WHERE column REGEXP 'pattern'

https://clickhouse.com/docs/sql-reference/functions/string-search-functions#match

---

To match groups use extract / extractAll functions