Tanner Clary created CALCITE-5644: ------------------------------------- Summary: Implement BigQuery CONTAINS_SUBSTR Key: CALCITE-5644 URL: https://issues.apache.org/jira/browse/CALCITE-5644 Project: Calcite Issue Type: Task Reporter: Tanner Clary
BigQuery offers the [CONTAINS_SUBSTR|https://cloud.google.com/bigquery/docs/reference/standard-sql/string_functions#contains_substr] function that returns {{TRUE}} if a substring is present in an expression and {{FALSE}} if it is not. A basic example of this may be seen in [1]. The expression can take many forms (more info in the linked doc) which makes its implementation more complex than other string functions that only accept arguments of type {{STRING}}. For instance, the expression to be searched can be a column or table reference. The function also has an optional third argument called {{json_scope}} where the user can indicate the scope of JSON data (keys, values, or both) to be searched for the substring. I am curious if anyone has thoughts on how the search of rows or tables could be implemented. I have a basic implementation (that supports expressions of type {{STRING}} and nothing else) that I will open a draft PR for as a starting point. To me, the challenge is implementing the additional features like the {{JSON_SCOPE}} argument (seen in [2]) and performing a cross field search as seen in [3]. [1] {{SELECT CONTAINS_SUBSTR("hello", "he");}} would return {{TRUE}}. [2] {{SELECT * FROM Recipes WHERE CONTAINS_SUBSTR( (SELECT AS STRUCT Recipes.* EXCEPT (Lunch, Dinner)), 'potato' );}} would return: /*-------------------+-------------------------+------------------* | Breakfast | Lunch | Dinner | +-------------------+-------------------------+------------------+ | Potato pancakes | Toasted cheese sandwich | Beef stroganoff | *-------------------+-------------------------+------------------*/ -- This message was sent by Atlassian Jira (v8.20.10#820010)