isearch function

Applies to: check marked yes Databricks Runtime 19.0 and above

Important

This feature is in Beta.

Performs a case-insensitive search for text within specified target expressions.

isearch is the case-insensitive variant of search function with otherwise identical behavior.

Syntax

isearch ( expr [, ...], search_pattern [, mode => mode] )

Arguments

  • expr: The expressions to search within.
  • search_pattern: A constant STRING expression indicating the text to search for.
  • mode: Optional. A case-insensitive STRING literal that controls how matching is performed. Valid values are 'substring' and 'word'. The default value is 'substring'.

Returns

A BOOLEAN value that indicates whether the search pattern matches any string-searchable target expression, ignoring case.

Notes

In substring mode, the search pattern is matched literally. Characters such as ., *, and % have no special meaning as regular expression or wildcard characters.

The following types are string-searchable:

Type String-searchable when
VOID, STRING, VARIANT Always.
ARRAY Its child expression is string-searchable.
MAP Its value expression is string-searchable.
STRUCT At least one of its field expressions is string-searchable.

For STRUCT expressions, isearch ignores fields that are not string-searchable.

For VARIANT expressions, isearch scans only their leaf nodes that are of type VOID or STRING.

The isearch expression does not implicitly cast non-STRING expressions to STRING expressions.

The return value is determined as follows:

  • NULL if the search pattern is NULL.
  • true if the search pattern matches at least one string-searchable value, ignoring case.
  • NULL if the search pattern doesn't match any string-searchable value, and at least one of those values is NULL.
  • false if the search pattern doesn't match any string-searchable value, and none of those values is NULL.

The following values are supported for the mode:

  • 'substring': Matches if the search pattern appears anywhere within the target value, ignoring case. This is the default option.
  • 'word': The search pattern is split into words at UAX#29 word boundaries. The mode matches the individual words in the search pattern regardless of their order. Each pattern word matches a string-searchable value if it appears in the value with UAX#29 word boundaries immediately before and after it, ignoring case. A pattern that contains no words, such as '!!!' or ' ', raises SEARCH_INVALID_WORD_PATTERN.

The isearch performance on the table can benefit from a prebuilt full-text search index. See Full-text search indexes on Unity Catalog managed tables for details.

Common error conditions

Examples

-- Basic examples.

-- Substring mode (default).
> SELECT isearch(column, 'needle', mode => 'substring') FROM VALUES ('Needle') AS table(column);
 true

> SELECT isearch(column, 'quick fox', mode => 'substring') FROM VALUES ('quick brown fox') AS table(column);
 false

> SELECT isearch(column, 'FOO.BAR', mode => 'substring') FROM VALUES ('prefix foo.bar suffix') AS table(column);
 true -- the period is matched literally, and letter case is ignored.

> SELECT isearch(column, 'FOO.BAR', mode => 'substring') FROM VALUES ('prefix fooXbar suffix') AS table(column);
 false -- the period does not match an arbitrary character.

> SELECT isearch(column, '100%', mode => 'substring') FROM VALUES ('100 percent'), ('100%') AS table(column);
 false
 true -- the percent sign is not a wildcard.

> SELECT isearch(column, lower('NEEDLE')) FROM VALUES ('needle') AS table(column);
 true

-- Non-ASCII case-insensitive matching.
> SELECT isearch(column, 'κόσμος', mode => 'substring') FROM VALUES ('ΚΌΣΜΟΣ') AS table(column);
 true

> SELECT isearch(column, 'οσ', mode => 'substring') FROM VALUES ('ΟΣ') AS table(column);
 true -- sigma folding (`σ` and final `ς`).

-- Word mode.
> SELECT isearch(column, 'fox quick', mode => 'word') FROM VALUES ('Quick Brown Fox') AS table(column);
 true

> SELECT isearch(column, 'slow fox', mode => 'word') FROM VALUES ('quick fox') AS table(column);
 false

> SELECT isearch(column, 'FOO*BAR', mode => 'word') FROM VALUES ('bar and foo') AS table(column);
 true -- the asterisk separates the pattern words; it is not a wildcard.

-- Examples on a table with a VARIANT column.
> CREATE TABLE test_table AS
    SELECT parse_json('{
      "role": "user",
      "id": 101,
      "preferences": {"theme": "dark", "language": "en"}
    }') AS column;

> SELECT isearch(column, 'USER', mode => 'substring') FROM test_table;
 true

> SELECT isearch(column, 'PREFERENCES', mode => 'substring') FROM test_table;
 false -- only values are searched.

> SELECT isearch(column, '101', mode => 'substring') FROM test_table;
 false -- no implicit cast to string.

-- NULL behavior.
> SELECT isearch(NULL, 'needle', mode => 'substring');
 NULL

> SELECT isearch(CAST(NULL AS STRING), 'needle', mode => 'substring');
 NULL

> SELECT isearch(CAST(NULL AS INT), 'needle', mode => 'substring');
 false

> SELECT isearch('needle in haystack', NULL, mode => 'substring');
 NULL

-- Search across multiple columns.
> SELECT isearch(column1, column2, '123', mode => 'substring')
    FROM VALUES (123, 'needle in haystack') AS table(column1, column2);
 false -- integer column is skipped, no implicit cast to string.

> SELECT isearch(column1, column2, 'NEEDLE', mode => 'substring')
    FROM VALUES (123, 'needle in haystack') AS table(column1, column2);
 true
  • Last updated on