search function

Applies to: check marked yes Databricks Runtime 19.0 and above

Important

This feature is in Beta.

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

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

-- 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, 'foo.bar', mode => 'substring') FROM VALUES ('prefix foo.bar suffix') AS table(column);
 true -- the period is matched literally.

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

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

> 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

> SELECT search(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 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
  • Last updated on