How to search the string in query with case insensitive on Clickhouse database?

Question

I am developing site using PHP and backend ClickHouse database. When i using like queries , it is not supporting case-sensitive words.

select id,comments from discussion where  comments LIKE "%Data not reflect%";

Is there any way to search case-insensitive words?

Answer 1

There's no ILIKE operator. I think you can use lowerUTF8().

select id,comments from discussion where lowerUTF8(comments) LIKE '%Data not reflect%';

However, might be performance heavy as it will have to convert all comments values to lowercase.

Answer 2

Case insensitive ILIKE operator was added to CH starting with version 20.6.3.28:

SELECT *
FROM
(
    SELECT '** Data not reflect **' AS text
    UNION ALL
    SELECT '** data not reflect **'
)
WHERE text ILIKE '%Data not reflect%'

/*
┌─text───────────────────┐
│ ** Data not reflect ** │
│ ** data not reflect ** │
└────────────────────────┘
*/

Answer 3

Use positionCaseInsensitive or positionCaseInsensitiveUTF8

Just like that

SELECT id,comments
FROM discussion
WHERE positionCaseInsensitive(comments,'Data not reflect')>0;

For more complicated patterns you can use regular expression with i flag inside:

SELECT ... WHERE match(comment, '(?i)Data.*not reflect');

See documentation: https://clickhouse.yandex/docs/en/query_language/functions/string_search_functions/#position-haystack-needle-locate-haystack-needle