Aller au contenu principal
Dernière mise à jour le

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

Performs a search for text within specified target expressions.

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

Syntax​

search ( 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.

Notes​

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.

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, search ignores fields that are not string-searchable.

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

The search 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.
  • 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. 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. A pattern that contains no words, such as '!!!' or ' ', raises SEARCH_INVALID_WORD_PATTERN.

The search 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​

SQL
-- Basic examples.

-- Substring mode (default).
> 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

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

> SELECT search(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 search(column, 'user', mode => 'substring') FROM test_table;
 true

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

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

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

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

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

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

-- Search across multiple columns.
> SELECT search(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 search(column1, column2, 'needle', mode => 'substring')
    FROM VALUES (123, 'needle in haystack') AS table(column1, column2);
 true
Sur cette page