isearch function

Applies to: Databricks Runtime 19.0 and above

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

The following types are 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

• DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE
• NOT_ENOUGH_ARGS
• SEARCH_INVALID_WORD_PATTERN
• SEARCH_STAR_ARGUMENT_NOT_ALLOWED

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, 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

-- 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

Related functions

• search function
• contains function
• ilike operator
• like operator
• rlike operator