regexdatabaseclickhouse

Clickhouse query use regular expression

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.

Solution

  • 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