DOCSUP-3478: Documented the iLike function #15880

sevirov · 2020-10-12T21:07:11Z

I hereby agree to the terms of the CLA available at: https://yandex.ru/legal/cla/?lang=en

Changelog category:

Documentation for ILIKE operator #12125

Добавил описание функции iLike и добавил оператор ILIKE.

BayoNet · 2020-10-13T15:42:57Z

docs/en/sql-reference/functions/string-search-functions.md

@@ -378,6 +378,57 @@ For other regular expressions, the code is the same as for the ‘match’ funct

 The same thing as ‘like’, but negative.

+## iLike {#ilike}
+
+Case insensitive variant of function `like`. It should respect UTF-8 and work similar to `positionCaseInsensitiveUTF8`.


If we refer to something, for example functions, we need to provide links.
I have understood nothing about UTF-8 reference.

BayoNet · 2020-10-13T15:55:46Z

docs/en/sql-reference/functions/string-search-functions.md

+
+**Returned value(s)**
+
+-   True, if the string matches the supplied pattern.


Suggested change

- True, if the string matches the supplied pattern.

- True, if the string matches `pattern`.

BayoNet · 2020-10-13T15:56:03Z

docs/en/sql-reference/functions/string-search-functions.md

+**Returned value(s)**
+
+-   True, if the string matches the supplied pattern.
+-   False, if the string doesn't match the supplied pattern.


Suggested change

- False, if the string doesn't match the supplied pattern.

- False, if the string doesn't match `pattern`.

BayoNet · 2020-10-13T15:56:14Z

docs/en/sql-reference/functions/string-search-functions.md

+**Parameters**
+
+-   `haystack` — Input string. [String](../../sql-reference/syntax.md#syntax-string-literal).
+-   `pattern` — Regular expression with [re2 syntax](https://github.com/google/re2/wiki/Syntax). Must contain groups, each group enclosed in parentheses. If pattern contains no groups, an exception is thrown. [String](../../sql-reference/syntax.md#syntax-string-literal).


The like function description contains "The regular expression can contain the meta symbols % and _." Does the function really require re2, or is there custom pattern syntax with "%" and "_" special symbols?

The function definitely must not contain groups. In fact, I'm not sure that it can contain groups.

I almost sure, the description of these functions must be similar to the PostgreSQL description https://www.postgresql.org/docs/13/functions-matching.html, and this pattern is not a regular expression at all.

The like function description contains "The regular expression can contain the meta symbols % and ." Does the function really require re2, or is there custom pattern syntax with "%" and "" special symbols?

The function definitely must not contain groups. In fact, I'm not sure that it can contain groups.

I almost sure, the description of these functions must be similar to the PostgreSQL description https://www.postgresql.org/docs/13/functions-matching.html, and this pattern is not a regular expression at all.

Yes, I agree that this pattern is not a regular expression at all. Can I describe different cases of the pattern in the same as in the PostgreSQL? But that's a lot of words.

I didn't see many words about the pattern description in the Postgres docs, just a couple of sentences and four examples in one code block.

BayoNet · 2020-10-13T16:00:10Z

docs/en/sql-reference/functions/string-search-functions.md

+Query:
+
+``` sql
+SELECT * FROM Months WHERE ilike(name, '%j%')


Suggested change

SELECT * FROM Months WHERE ilike(name, '%j%')

SELECT * FROM Months WHERE iLike(name, '%j%')

iLike doesn't work, but ilike works. I tested it.

BayoNet · 2020-10-13T16:03:06Z

Also, you didn't mention that ClickHouse supports the ILIKE operator, which works the same as the function.

Changed by comments.

BayoNet · 2020-10-16T16:21:20Z

docs/en/sql-reference/functions/string-search-functions.md

@@ -378,6 +378,66 @@ For other regular expressions, the code is the same as for the ‘match’ funct

 The same thing as ‘like’, but negative.

+## iLike {#ilike}


If iLike doesn't work, the topic must be named ilike.

BayoNet · 2020-10-16T16:25:32Z

docs/en/sql-reference/functions/string-search-functions.md

@@ -378,6 +378,66 @@ For other regular expressions, the code is the same as for the ‘match’ funct

 The same thing as ‘like’, but negative.

+## iLike {#ilike}
+
+Case insensitive variant of [like](https://clickhouse.tech/docs/en/sql-reference/functions/string-search-functions/#function-like) function and works the same as the `ILIKE` operator.


The function doesn't work like ILIKE. ILIKE implementation is based on this function. You can write that the user can run ILIKE operator instead of the function.

BayoNet · 2020-10-16T16:26:27Z

docs/en/sql-reference/functions/string-search-functions.md

+-   `haystack` — Input string. [String](../../sql-reference/syntax.md#syntax-string-literal).
+-   `pattern` — If `pattern` doesn't contain percent signs or underscores, then the `pattern` only represents the string itself. An underscore (`_`) in `pattern` stands for (matches) any single character. A percent sign (`%`) matches any sequence of zero or more characters.
+
+Some examples of the `pattern` type:


Suggested change

Some examples of the `pattern` type:

Some `pattern` examples:

BayoNet · 2020-10-16T16:27:22Z

docs/en/sql-reference/operators/index.md

@@ -53,6 +53,8 @@ ClickHouse transforms operators to their corresponding functions at the query pa

 `a NOT LIKE s` – The `notLike(a, b)` function.

+`a ILIKE s` – The `iLike(a, b)` function.


iLike or ilike?

Внес поправки в английскую версию и сделал перевод на русский язык.

* master: (719 commits) Revert "scipy" DOCSUP-3478: Documented the iLike function (ClickHouse#15880) DOCSUP-2954: Documented the view function (ClickHouse#15881) DOCSUP-2861: Translate on Russian (ClickHouse#16042) fix for floats tests for nullable_aggregate_states Update settings.md Update replication.md Update replication.md Update replication.md Update settings.md test for the bug 8580 test for the bug ClickHouse#8490 test for the bug ClickHouse#8425 Update run.sh done remove warnings in integration tests reset error code in ast fuzzer fix segfault with wrong aggregation in lambdas restart the tests ... # Conflicts: # src/Access/IAccessStorage.cpp # src/Access/IAccessStorage.h # src/Access/LDAPAccessStorage.cpp # src/Access/LDAPAccessStorage.h # src/Access/LDAPParams.h # src/Access/MultipleAccessStorage.cpp # src/Common/ErrorCodes.cpp # src/Server/HTTPHandler.cpp

Description of the iLike function

f918c73

Добавил описание функции iLike и добавил оператор ILIKE.

robot-clickhouse added the pr-documentation Documentation PRs for the specific code PR label Oct 12, 2020

BayoNet added the can be tested label Oct 13, 2020

BayoNet reviewed Oct 13, 2020

View reviewed changes

Update string-search-functions.md

4164293

Changed by comments.

BayoNet reviewed Oct 16, 2020

View reviewed changes

Update and translation ilike function and ILIKE operator..

a725dbe

Внес поправки в английскую версию и сделал перевод на русский язык.

BayoNet merged commit e12f69c into ClickHouse:master Oct 19, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

DOCSUP-3478: Documented the iLike function #15880

DOCSUP-3478: Documented the iLike function #15880

sevirov commented Oct 12, 2020

BayoNet Oct 13, 2020

BayoNet Oct 13, 2020

BayoNet Oct 13, 2020

BayoNet Oct 13, 2020 •

edited

sevirov Oct 13, 2020

BayoNet Oct 15, 2020 •

edited

BayoNet Oct 13, 2020

sevirov Oct 13, 2020

BayoNet Oct 15, 2020

BayoNet commented Oct 13, 2020

BayoNet Oct 16, 2020

BayoNet Oct 16, 2020

BayoNet Oct 16, 2020

BayoNet Oct 16, 2020 •

edited


		Returned value(s)

		- True, if the string matches the supplied pattern.

	- True, if the string matches the supplied pattern.
	- True, if the string matches `pattern`.

	- False, if the string doesn't match the supplied pattern.
	- False, if the string doesn't match `pattern`.

	SELECT * FROM Months WHERE ilike(name, '%j%')
	SELECT * FROM Months WHERE iLike(name, '%j%')

		@@ -378,6 +378,66 @@ For other regular expressions, the code is the same as for the ‘match’ funct

		The same thing as ‘like’, but negative.

		## iLike {#ilike}

	Some examples of the `pattern` type:
	Some `pattern` examples:

		@@ -53,6 +53,8 @@ ClickHouse transforms operators to their corresponding functions at the query pa

		`a NOT LIKE s` – The `notLike(a, b)` function.

		`a ILIKE s` – The `iLike(a, b)` function.

DOCSUP-3478: Documented the iLike function #15880

DOCSUP-3478: Documented the iLike function #15880

Conversation

sevirov commented Oct 12, 2020

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

BayoNet Oct 13, 2020 • edited

Choose a reason for hiding this comment

Choose a reason for hiding this comment

BayoNet Oct 15, 2020 • edited

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

BayoNet commented Oct 13, 2020

Choose a reason for hiding this comment

Choose a reason for hiding this comment

Choose a reason for hiding this comment

BayoNet Oct 16, 2020 • edited

Choose a reason for hiding this comment

BayoNet Oct 13, 2020 •

edited

BayoNet Oct 15, 2020 •

edited

BayoNet Oct 16, 2020 •

edited