ScopeDB LogoDOCUMENTATION

search

Searches text-like data.

Syntax

search(
    <search_data>,
    query => <query>
    [, analyzer => <analyzer> ]
    [, mode => <mode> ]
)

Arguments

<search_data>

The data you want to search. This can be string, array, object, or any.

<query> (named)

A string that contains one or more search terms. This argument must be a literal string; column names are not supported. Specify one pair of single quotes around the entire string.

<analyzer> (named) (optional)

The analyzer to use for the search. Supported values:

  • log

The default value is log.

<mode> (named) (optional)

Controls how analyzed query tokens are matched. Supported values:

  • and: all unique query tokens must occur in the searched value. Their order does not matter.
  • phrase: the analyzed query tokens must occur consecutively and in order within the same string value. A phrase does not span separate strings inside an array or object.

The default value is and.

Returns

Returns a boolean.

  • In and mode, the value is TRUE if every unique <query> token is found in <search_data>.
  • In phrase mode, the value is TRUE if the analyzed phrase is found in a string within <search_data>.
  • Returns NULL if <search_data> is NULL. The query, analyzer, and mode parameters must be non-NULL string literals.
  • Otherwise, returns FALSE.

Examples

VALUES ('foo bar'), ('foobar'), ('bar baz'), ('') SELECT *, search($0, query => 'foo');
+---------+----------------------------+
| $0      | search($0, query => 'foo') |
+---------+----------------------------+
| foo bar | true                       |
| foobar  | false                      |
| bar baz | false                      |
|         | false                      |
+---------+----------------------------+

Compare the default and mode with phrase matching:

VALUES ('foo bar'), ('foo one bar'), ('bar foo'), ('foo')
SELECT
    $0 AS value,
    search($0, query => 'foo bar') AS all_tokens,
    search($0, query => 'foo bar', mode => 'phrase') AS exact_phrase;
+-------------+------------+--------------+
| value       | all_tokens | exact_phrase |
+-------------+------------+--------------+
| foo bar     | true       | true         |
| foo one bar | true       | false        |
| bar foo     | true       | false        |
| foo         | false      | false        |
+-------------+------------+--------------+