Skip to main content
Last updated on

isearch function

Applies to: check marked yes Databricks Runtime 19.0 and above

Beta

This feature is in Beta. Workspace admins can control access to this feature from the Previews page. See Manage Databricks previews.

Returns true if search_pattern is found in any of the supplied target column values, matching case-insensitively.

isearch is the case-insensitive variant of search function with otherwise identical behavior. See search function for full details on argument types, the * wildcard, STRUCT expansion, and the supported modes.

Syntax​

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

Arguments​

  • column: One or more searchable target expressions. See search for the list of supported types.
  • search_pattern: A foldable (constant) STRING expression with the value to search for.
  • mode: An optional named STRING argument. One of 'substring' (default), 'word', or 'ip'.

Returns​

A BOOLEAN.

  • true if search_pattern is found in any of the target column values, ignoring case.
  • NULL if search_pattern is not found in any target column value and at least one of those values is NULL.
  • false otherwise.

Common error conditions​

Examples​

SQL
-- isearch matches regardless of case.
> SELECT isearch(column, 'needle', mode => 'substring') FROM VALUES ('Needle') AS table(column);
 true

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

-- search returns false for the same input because of the case mismatch.
> SELECT search(column, 'NEEDLE') FROM VALUES ('needle in a haystack') AS table(column);
 false

-- Word mode is also case-insensitive.
> SELECT isearch(column, 'QUICK fox', mode => 'word') FROM VALUES ('Quick Brown Fox') AS table(column);
 true
On this page