search function

Applies to: check marked yes Databricks Runtime 19.0 and above

Important

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

Returns true if search_pattern is found in any of the supplied target column values.

For information about accelerating search queries with full-text search indexes, see Full-text search indexes on Unity Catalog managed tables.

Syntax

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

Arguments

  • column: One or more searchable target expressions. A searchable expression has one of these types:

    • STRING with UTF8_BINARY collation.
    • VARIANT.
    • STRUCT with at least one searchable field. The struct is automatically expanded to its searchable leaf fields at any nesting depth.
    • ARRAY of any searchable type.

    There is no implicit cast for non-string target values.

  • search_pattern: A foldable (constant) STRING expression with the value to search for.

  • mode: An optional named STRING argument that controls how matches are performed. One of:

    • 'substring' (default): Matches if search_pattern appears anywhere within a target value. Equivalent to contains function.
    • 'word': Matches the individual words in search_pattern against a target value, regardless of order.

Returns

A BOOLEAN.

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

Notes

  • isearch function is the case-insensitive variant of search with otherwise identical behavior.
  • For a STRUCT argument, the function searches every searchable leaf field reachable from the top-level struct, regardless of nesting depth. The same expansion applies recursively to STRUCT fields within VARIANT and ARRAY values.
  • In 'substring' mode, VARIANT keys and non-string scalar values inside a VARIANT are not matched. Use 'word' mode or extract individual fields to search those.

Common error conditions

Examples

-- Basic examples.
> SELECT search(column, 'needle', mode => 'substring') FROM VALUES ('Needle') AS table(column);
 false

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

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

> SELECT search(column, 'quick fox', mode => 'word') FROM VALUES ('quick brown fox') AS table(column);
 true

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

-- Automatic expansion of STRUCT columns to their searchable leaf fields.
> CREATE TABLE test_table (
    usage_stats STRUCT<digits: INT>,
    customer_info STRUCT<
      contact: STRUCT<json_field: VARIANT>,
      name: STRING>)
  USING DELTA;

> SELECT * FROM test_table WHERE search(usage_stats.digits, 'needle');
 [DATATYPE_MISMATCH.UNEXPECTED_INPUT_TYPE]

> SELECT * FROM test_table WHERE search(customer_info, 'needle');
-- Equivalent to:
> SELECT * FROM test_table
  WHERE search(customer_info.contact.json_field, customer_info.name, 'needle');

> SELECT * FROM test_table WHERE search(customer_info.contact, 'needle');
-- Equivalent to:
> SELECT * FROM test_table
  WHERE search(customer_info.contact.json_field, 'needle');

-- VARIANT behavior: substring mode does not match keys or non-string scalar values.
> CREATE TABLE test_table AS
  SELECT parse_json('{
    "role": "user",
    "id": 101,
    "preferences": {"theme": "dark", "language": "en"}
  }') AS column;

> SELECT search(column, 'user', mode => 'substring') FROM test_table;
 true

> SELECT search(column, 'preferences', mode => 'substring') FROM test_table;
 false

> SELECT search(column, '101', mode => 'substring') FROM test_table;
 false

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

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

> SELECT search('needle in haystack', NULL, mode => 'substring');
 [SEARCH_REQUIRES_STRING_LITERALS_ARGUMENTS]
  • Last updated on