- Categories:
String & binary functions (Full-Text Search)
SEARCH¶
Searches character data (text) in specified columns from one or more tables, including fields in VARIANT, OBJECT, and ARRAY columns. A text analyzer breaks the text into tokens, which are discrete units of text, such as words or numbers. A default analyzer is applied if you do not specify one.
For more information about using this function, see Using full-text search.
Syntax¶
SEARCH( <search_data>, <search_string> [ , ANALYZER => '<analyzer_name>' ] )
Arguments¶
search_dataThe data you want to search, expressed as a comma-delimited list of string literals, column names, or paths to fields in VARIANT columns. The search data can also be a single literal string, which can be useful when you are testing the function.
You can specify the wildcard character (
*), where*expands to all qualifying columns in all of the tables that are in scope for the function. Qualifying columns are those that have VARCHAR (text), VARIANT, ARRAY, and OBJECT data types. VARIANT, ARRAY, and OBJECT data is converted to text for searching.When you pass a wildcard to the function, you can qualify the wildcard with the name or alias for the table. For example, to pass in all of the columns from the table named
mytable, specify the following:(mytable.*)
You can also use the ILIKE and EXCLUDE keywords for filtering:
ILIKE filters for column names that match the specified pattern. Only one pattern is allowed. For example:
(* ILIKE 'col1%')
EXCLUDE filters out column names that don’t match the specified column or columns. For example:
(* EXCLUDE col1) (* EXCLUDE (col1, col2))
Qualifiers are valid when you use these keywords. The following example uses the ILIKE keyword to filter for all of the columns that match the pattern
col1%in the tablemytable:(mytable.* ILIKE 'col1%')
The ILIKE and EXCLUDE keywords can’t be combined in a single function call.
For more information about the ILIKE and EXCLUDE keywords, see the “Parameters” section in SELECT.
You can search columns from more than one table when multiple tables are in scope by joining tables or using the UNION set operator. To search all of the columns in the output of a join or a UNION query, you can use the unqualified
*wildcard as follows:SELECT * FROM t AS T1 JOIN t AS T2 USING (col1) WHERE SEARCH((*), 'string');
To search specific columns when joining tables, you might need to qualify the column names (for example,
table2.colname). You can also use a qualified*wildcard as follows:SELECT * FROM t AS T1 JOIN t AS T2 USING (col1) WHERE SEARCH((T2.*), 'string');
However, note that you cannot specify
*ortable.*more than once for the function. In the previous join example, you could not specifySEARCH((T1.*, T2.*), 'string'). This syntax returns an error.Parentheses are required for the
search_dataargument when*,table.*, or multiple items are listed. For example:SEARCH((col1, col2, col3), 'string') SEARCH((t1.*), 'string') SEARCH((*), 'string')
If parentheses are not used to separate multiple items, commas are parsed as separators between function arguments.
See also Examples of Expected Error Cases.
You can search fields in VARIANT data by specifying the column name, a colon or dot, and the subfields separated by dots. For example:
colname:fieldname.subfieldname. For more information about specifying fields in such columns, see Traversing Semi-structured Data.search_stringA VARCHAR 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. Do not specify quotes around individual terms or phrases. For example, use:
'blue red green'Do not use:
'blue' 'red' 'green'The list of terms is disjunctive. In this case, the search would look for rows that contain
blueORredORgreen. However, when the NO_OP_ANALYZER is used, the query string is matched exactly as it is, with no tokenization and no disjunctive semantics.Searches are not case sensitive (except when the NO_OP_ANALYZER is used), so a search for the term
'ONCE'against the string'Once upon a time'returns TRUE.The order of search terms does not matter, with respect to their presence in the searched data.
ANALYZER => 'analyzer_name'Optional argument that specifies the name of the text analyzer. The name must be enclosed in single quotes.
The analyzer breaks the search terms (and the text from the column being searched) into tokens. A row matches if any of the tokens extracted from the search string exactly matches a token extracted from any of the columns or fields being searched.
The analyzer tokenizes a string by breaking it where it finds certain delimiters. These delimiters are not included in the resulting tokens, and empty tokens are not extracted.
This parameter accepts one of the following values:
DEFAULT_ANALYZER: Breaks text into tokens based on the following delimiters:
Character
Unicode code
Description
U+0020Space
[U+005BLeft square bracket
]U+005DRight square bracket
;U+003BSemicolon
<U+003CLess-than sign
>U+003EGreater-than sign
(U+0028Left parenthesis
)U+0029Right parenthesis
{U+007BLeft curly bracket
}U+007DRight curly bracket
|U+007CVertical bar
!U+0021Exclamation mark
,U+002CComma
'U+0027Apostrophe
"U+0022Quotation mark
*U+002AAsterisk
&U+0026Ampersand
?U+003FQuestion mark
+U+002BPlus sign
/U+002FSlash
:U+003AColon
=U+003DEqual sign
@U+0040At sign
.U+002EPeriod (full stop)
-U+002DHyphen
$U+0024Dollar sign
%U+0025Percent sign
\U+005CBackslash
_U+005FUnderscore (low line)
\nU+000ANew line (line feed)
\rU+000DCarriage return
\tU+0009Horizontal tab
UNICODE_ANALYZER: Tokenizes based on Unicode segmentation rules that treat spaces and certain punctuation characters as delimiters. These internal rules are designed for natural language searches (in many different languages). For example, the default analyzer treats periods in IP addresses and apostrophes in contractions as delimiters, but the Unicode analyzer does not. See Using an analyzer to adjust search behavior.
For more information about the Unicode Text Segmentation algorithm, see https://unicode.org/reports/tr29/ (https://unicode.org/reports/tr29/).
NO_OP_ANALYZER: Tokenizes neither the data nor the query string. A search term must exactly match the full text in a column or field, including case sensitivity; otherwise, the SEARCH function returns FALSE. Even if the query string looks like it contains multiple tokens (for example,
'sky blue'), the column or field must equal the entire query string exactly. In this case, only'sky blue'is a match;'sky'and'blue'are not matches.
For more information about the behavior of different analyzers, see How search terms are tokenized.
Returns¶
Returns a BOOLEAN.
The value is TRUE if any
search_stringtokens are found insearch_data.Returns NULL if either of these arguments is NULL.
Otherwise, returns FALSE.
Usage notes¶
The SEARCH function operates only on VARCHAR, VARIANT, ARRAY, and OBJECT data. The function returns an error if the
search_dataargument doesn’t contain data of these data types. When thesearch_dataargument includes data of both supported data types and unsupported data types, the function searches the data of the supported data types and silently ignores the data of the unsupported data types. For examples, see Examples of expected error cases.You can add a FULL_TEXT search optimization on columns that are the target of SEARCH function calls by using an ALTER TABLE command. For example:
ALTER TABLE lines ADD SEARCH OPTIMIZATION ON FULL_TEXT(play, character, line);
For more information, see enable FULL_TEXT search optimization.
How search terms are tokenized¶
The following table shows a few examples of how input search terms are split into tokens, which depends on the rules applied by the analyzer that is used. In the table, commas denote where the tokens are split (if they are).
Search Term(s) |
Tokens: DEFAULT_ANALYZER |
Tokens: UNICODE_ANALYZER |
NO_OP_ANALYZER (not split) |
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Examples¶
The following examples show different ways to use the SEARCH function, starting with simple usage and progressing to more complex use cases.
Matching against a literal¶
The simplest example of the SEARCH function is a test for TRUE or FALSE on a string literal. The first example returns TRUE because the literals for the first and second arguments match, given that the comparison is not case sensitive.
SELECT SEARCH('king','KING');
+-----------------------------+
| SEARCH('KING','KING') |
|-----------------------------|
| True |
+-----------------------------+
The second example returns FALSE because the token 32 does not appear in the literal 5.1.33 specified
for the first argument.
SELECT SEARCH('5.1.33','32');
+-----------------------------+
| SEARCH('5.1.33','32') |
|-----------------------------|
| False |
+-----------------------------+
Matching against a column reference¶
This example uses a column in a table as the first argument. The function returns TRUE because one of
the search terms (king) exists in the character column. The list of terms is disjunctive.
(For information about the table used here and in some subsequent examples, see Sample data for SEARCH.)
SELECT SEARCH(character, 'king queen'),character
FROM lines
WHERE line_id=4;
+--------------------------------------+---------------+
| SEARCH(CHARACTER,'KING QUEEN') | CHARACTER |
|--------------------------------------+---------------|
| True | KING HENRY IV |
+--------------------------------------+---------------+
WHERE clause search on one column¶
The following query uses the SEARCH function to find rows that contain the word “wherefore” in the line column:
SELECT *
FROM lines
WHERE SEARCH(line, 'wherefore')
ORDER BY character LIMIT 5;
+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+
| LINE_ID | PLAY | SPEECH_NUM | ACT_SCENE_LINE | CHARACTER | LINE |
|---------+----------------------+------------+----------------+-----------+-----------------------------------------------------|
| 100109 | Troilus and Cressida | 31 | 2.1.53 | ACHILLES | Why, how now, Ajax! wherefore do you thus? How now, |
| 16448 | As You Like It | 2 | 2.3.6 | ADAM | And wherefore are you gentle, strong and valiant? |
| 24055 | The Comedy of Errors | 14 | 5.1.41 | AEMELIA | Be quiet, people. Wherefore throng you hither? |
| 99330 | Troilus and Cressida | 30 | 1.1.102 | AENEAS | How now, Prince Troilus! wherefore not afield? |
| 92454 | The Tempest | 150 | 2.1.343 | ALONSO | Wherefore this ghastly looking? |
+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+
WHERE clause search on multiple columns¶
The following query uses the SEARCH function to find rows that contain the word “king” in the play column,
the character column, or both columns. Parentheses are required for the first argument.
SELECT play, character
FROM lines
WHERE SEARCH((play, character), 'king')
ORDER BY play, character LIMIT 10;
+---------------------------+-----------------+
| PLAY | CHARACTER |
|---------------------------+-----------------|
| All's Well That Ends Well | KING |
| Hamlet | KING CLAUDIUS |
| Hamlet | KING CLAUDIUS |
| Henry IV Part 1 | KING HENRY IV |
| Henry IV Part 1 | KING HENRY IV |
| King John | CHATILLON |
| King John | KING JOHN |
| King Lear | GLOUCESTER |
| King Lear | KENT |
| Richard II | KING RICHARD II |
+---------------------------+-----------------+
Wildcard search on all qualifying columns in a table¶
You can use the * character (or table.*) as the first argument to the SEARCH function, as shown in this example.
The search operates on all of the qualifying columns in the table you are selecting from, which in this case is the lines table.
The lines table has four columns that have data types supported by the search function. Note that the result consists of rows where “king”
appears in one or more of the four searched columns. For one of these columns, act_scene_line, the function finds no matches, but
the other three columns all have matches.
SELECT play, character, line, act_scene_line
FROM lines
WHERE SEARCH((lines.*), 'king')
ORDER BY act_scene_line LIMIT 10;
+-----------------+-----------------+----------------------------------------------------+----------------+
| PLAY | CHARACTER | LINE | ACT_SCENE_LINE |
|-----------------+-----------------+----------------------------------------------------+----------------|
| Pericles | LODOVICO | This king unto him took a fere, | 1.0.21 |
| Richard II | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster, | 1.1.1 |
| Henry VI Part 3 | WARWICK | I wonder how the king escaped our hands. | 1.1.1 |
| King John | KING JOHN | Now, say, Chatillon, what would France with us? | 1.1.1 |
| King Lear | KENT | I thought the king had more affected the Duke of | 1.1.1 |
| Henry IV Part 1 | KING HENRY IV | So shaken as we are, so wan with care, | 1.1.1 |
| Henry IV Part 1 | KING HENRY IV | Which, like the meteors of a troubled heaven, | 1.1.10 |
| King Lear | GLOUCESTER | so often blushed to acknowledge him, that now I am | 1.1.10 |
| Cymbeline | First Gentleman | Is outward sorrow, though I think the king | 1.1.10 |
| King John | CHATILLON | To this fair island and the territories, | 1.1.10 |
+-----------------+-----------------+----------------------------------------------------+----------------+
You can also use the ILIKE and EXCLUDE keywords for filtering. For more information about these keywords, see SELECT.
This search uses the ILIKE keyword to search only in columns that end with the string line.
SELECT play, character, line, act_scene_line
FROM lines
WHERE SEARCH((lines.* ILIKE '%line'), 'king')
ORDER BY act_scene_line LIMIT 10;
+-----------------+-----------------+--------------------------------------------------+----------------+
| PLAY | CHARACTER | LINE | ACT_SCENE_LINE |
|-----------------+-----------------+--------------------------------------------------+----------------|
| Pericles | LODOVICO | This king unto him took a fere, | 1.0.21 |
| Henry VI Part 3 | WARWICK | I wonder how the king escaped our hands. | 1.1.1 |
| King Lear | KENT | I thought the king had more affected the Duke of | 1.1.1 |
| Cymbeline | First Gentleman | Is outward sorrow, though I think the king | 1.1.10 |
+-----------------+-----------------+--------------------------------------------------+----------------+
This search uses the EXCLUDE keyword so that the function doesn’t search the data in the character column.
SELECT play, character, line, act_scene_line
FROM lines
WHERE SEARCH((lines.* EXCLUDE character), 'king')
ORDER BY act_scene_line LIMIT 10;
+-----------------+-----------------+----------------------------------------------------+----------------+
| PLAY | CHARACTER | LINE | ACT_SCENE_LINE |
|-----------------+-----------------+----------------------------------------------------+----------------|
| Pericles | LODOVICO | This king unto him took a fere, | 1.0.21 |
| Henry VI Part 3 | WARWICK | I wonder how the king escaped our hands. | 1.1.1 |
| King John | KING JOHN | Now, say, Chatillon, what would France with us? | 1.1.1 |
| King Lear | KENT | I thought the king had more affected the Duke of | 1.1.1 |
| Cymbeline | First Gentleman | Is outward sorrow, though I think the king | 1.1.10 |
| King Lear | GLOUCESTER | so often blushed to acknowledge him, that now I am | 1.1.10 |
| King John | CHATILLON | To this fair island and the territories, | 1.1.10 |
+-----------------+-----------------+----------------------------------------------------+----------------+
Wildcard search in a SELECT list¶
You can use the * character (or table.*) in a SELECT list, as shown in these examples.
The following search operates on all of the qualifying columns in the table you are selecting from, which in
this case is the lines table. The search returns True when “king” appears in one or more of the four
searched columns.
SELECT SEARCH((*), 'king') result, *
FROM lines
ORDER BY act_scene_line LIMIT 10;
+--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------+
| RESULT | LINE_ID | PLAY | SPEECH_NUM | ACT_SCENE_LINE | CHARACTER | LINE |
|--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------|
| True | 75787 | Pericles | 178 | 1.0.21 | LODOVICO | This king unto him took a fere, |
| True | 43494 | King John | 1 | 1.1.1 | KING JOHN | Now, say, Chatillon, what would France with us? |
| True | 49031 | King Lear | 1 | 1.1.1 | KENT | I thought the king had more affected the Duke of |
| True | 78407 | Richard II | 1 | 1.1.1 | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster, |
| False | 67000 | A Midsummer Night's Dream | 1 | 1.1.1 | THESEUS | Now, fair Hippolyta, our nuptial hour |
| True | 4 | Henry IV Part 1 | 1 | 1.1.1 | KING HENRY IV | So shaken as we are, so wan with care, |
| False | 12664 | All's Well That Ends Well | 1 | 1.1.1 | COUNTESS | In delivering my son from me, I bury a second husband. |
| True | 9526 | Henry VI Part 3 | 1 | 1.1.1 | WARWICK | I wonder how the king escaped our hands. |
| False | 52797 | Love's Labour's Lost | 1 | 1.1.1 | FERDINAND | Let fame, that all hunt after in their lives, |
| True | 28487 | Cymbeline | 3 | 1.1.10 | First Gentleman | Is outward sorrow, though I think the king |
+--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------+
You can also use the ILIKE and EXCLUDE keywords for filtering. For more information about these keywords, see SELECT.
This search uses the ILIKE keyword to search only in columns that end with the string line.
SELECT SEARCH(* ILIKE '%line', 'king') result, play, character, line
FROM lines
ORDER BY act_scene_line LIMIT 10;
+--------+---------------------------+-----------------+--------------------------------------------------------+
| RESULT | PLAY | CHARACTER | LINE |
|--------+---------------------------+-----------------+--------------------------------------------------------|
| True | Pericles | LODOVICO | This king unto him took a fere, |
| False | King John | KING JOHN | Now, say, Chatillon, what would France with us? |
| True | King Lear | KENT | I thought the king had more affected the Duke of |
| False | Richard II | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster, |
| False | A Midsummer Night's Dream | THESEUS | Now, fair Hippolyta, our nuptial hour |
| False | Henry IV Part 1 | KING HENRY IV | So shaken as we are, so wan with care, |
| False | All's Well That Ends Well | COUNTESS | In delivering my son from me, I bury a second husband. |
| True | Henry VI Part 3 | WARWICK | I wonder how the king escaped our hands. |
| False | Love's Labour's Lost | FERDINAND | Let fame, that all hunt after in their lives, |
| True | Cymbeline | First Gentleman | Is outward sorrow, though I think the king |
+--------+---------------------------+-----------------+--------------------------------------------------------+
This search uses the EXCLUDE keyword so that the function doesn’t search the data in the play or line columns.
SELECT SEARCH(* EXCLUDE (play, line), 'king') result, play, character, line
FROM lines
ORDER BY act_scene_line LIMIT 10;
+--------+---------------------------+-----------------+--------------------------------------------------------+
| RESULT | PLAY | CHARACTER | LINE |
|--------+---------------------------+-----------------+--------------------------------------------------------|
| False | Pericles | LODOVICO | This king unto him took a fere, |
| True | King John | KING JOHN | Now, say, Chatillon, what would France with us? |
| False | King Lear | KENT | I thought the king had more affected the Duke of |
| True | Richard II | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster, |
| False | A Midsummer Night's Dream | THESEUS | Now, fair Hippolyta, our nuptial hour |
| True | Henry IV Part 1 | KING HENRY IV | So shaken as we are, so wan with care, |
| False | All's Well That Ends Well | COUNTESS | In delivering my son from me, I bury a second husband. |
| False | Henry VI Part 3 | WARWICK | I wonder how the king escaped our hands. |
| False | Love's Labour's Lost | FERDINAND | Let fame, that all hunt after in their lives, |
| False | Cymbeline | First Gentleman | Is outward sorrow, though I think the king |
+--------+---------------------------+-----------------+--------------------------------------------------------+
Wildcard search on qualifying columns in joined tables¶
This example uses two small tables that contain information about car models. Table t1 has
two character columns, and table t2 has three. You can create and load the tables as follows:
CREATE OR REPLACE TABLE t1 (col1 INT, col2 VARCHAR(20), col3 VARCHAR(20));
INSERT INTO t1 VALUES
(1,'Mini','Cooper'),
(2,'Mini','Cooper S'),
(3,'Mini','Countryman'),
(4,'Mini','Countryman S');
CREATE OR REPLACE TABLE t2 (col1 INT, col2 VARCHAR(20), col3 VARCHAR(20), col4 VARCHAR(20));
INSERT INTO t2 VALUES
(1,'Mini','Cooper', 'Convertible'),
(2,'Mini','Cooper S', 'Convertible'),
(3,'Mini','Countryman SE','ALL4'),
(4,'Mini','Countryman S','ALL4');
The results of the following two queries differ, given the search over t1.* and t2.*.
Only two columns from t1 qualify for the search, but three columns from t2 qualify.
SELECT * FROM t1 JOIN t2 USING(col1)
WHERE SEARCH((t1.*),'s all4');
+------+------+--------------+------+--------------+-------------+
| COL1 | COL2 | COL3 | COL2 | COL3 | COL4 |
|------+------+--------------+------+--------------+-------------|
| 2 | Mini | Cooper S | Mini | Cooper S | Convertible |
| 4 | Mini | Countryman S | Mini | Countryman S | ALL4 |
+------+------+--------------+------+--------------+-------------+
SELECT * FROM t1 JOIN t2 USING(col1)
WHERE SEARCH((t2.*),'s all4');
+------+------+--------------+------+---------------+-------------+
| COL1 | COL2 | COL3 | COL2 | COL3 | COL4 |
|------+------+--------------+------+---------------+-------------|
| 2 | Mini | Cooper S | Mini | Cooper S | Convertible |
| 3 | Mini | Countryman | Mini | Countryman SE | ALL4 |
| 4 | Mini | Countryman S | Mini | Countryman S | ALL4 |
+------+------+--------------+------+---------------+-------------+
Wildcard search on the output of a UNION subquery¶
The following example uses the same two tables as the previous example. In this case,
the search is applied to all qualifying columns from t3, which is the table that results
from the subquery. The subquery computes the UNION of the first three columns in t1 and
t2 (five rows). The search returns two matching rows from the UNION result.
SELECT *
FROM (
SELECT col1, col2, col3 FROM t1
UNION
SELECT col1, col2, col3 FROM t2
) AS T3
WHERE SEARCH((T3.*),'s');
+------+------+--------------+
| COL1 | COL2 | COL3 |
|------+------+--------------|
| 2 | Mini | Cooper S |
| 4 | Mini | Countryman S |
+------+------+--------------+
Finding rows that match multiple search strings¶
The following example shows how to use SEARCH twice in the same query, so that both search strings have to return TRUE in order for a row to qualify for the result.
SELECT act_scene_line, character, line
FROM lines
WHERE SEARCH(line, 'Rosencrantz')
AND SEARCH(line, 'Guildenstern')
AND act_scene_line IS NOT NULL;
+----------------+------------------+-----------------------------------------------------------+
| ACT_SCENE_LINE | CHARACTER | LINE |
|----------------+------------------+-----------------------------------------------------------|
| 2.2.1 | KING CLAUDIUS | Welcome, dear Rosencrantz and Guildenstern! |
| 2.2.35 | KING CLAUDIUS | Thanks, Rosencrantz and gentle Guildenstern. |
| 2.2.36 | QUEEN GERTRUDE | Thanks, Guildenstern and gentle Rosencrantz: |
| 2.2.241 | HAMLET | Guildenstern? Ah, Rosencrantz! Good lads, how do ye both? |
| 4.6.27 | HORATIO | where I am. Rosencrantz and Guildenstern hold their |
| 5.2.60 | HORATIO | So Guildenstern and Rosencrantz go to't. |
| 5.2.389 | First Ambassador | That Rosencrantz and Guildenstern are dead: |
+----------------+------------------+-----------------------------------------------------------+
Searching VARIANT and VARCHAR data in a join¶
The following example shows a join of two tables, car_rentals and car_sales, with the search applied
to columns in both tables. The car_sales table contains VARIANT data. The car_sales table and its data
are described under Querying Semi-structured Data. The following SQL statements create the
car_rentals table and insert data into it:
CREATE OR REPLACE TABLE car_rentals(
vehicle_make varchar(30),
dealership varchar(30),
salesperson varchar(30));
INSERT INTO car_rentals VALUES
('Toyota', 'Tindel Toyota', 'Greg Northrup'),
('Honda', 'Valley View Auto Sales', 'Frank Beasley'),
('Tesla', 'Valley View Auto Sales', 'Arturo Sandoval');
Run the query:
SELECT SEARCH((r.vehicle_make, r.dealership, s.src:dealership), 'Toyota Tesla')
AS contains_toyota_tesla, r.vehicle_make, r.dealership,s.src:dealership
FROM car_rentals r JOIN car_sales s
ON r.SALESPERSON=s.src:salesperson.name;
+-----------------------+--------------+------------------------+--------------------------+
| CONTAINS_TOYOTA_TESLA | VEHICLE_MAKE | DEALERSHIP | S.SRC:DEALERSHIP |
|-----------------------+--------------+------------------------+--------------------------|
| True | Toyota | Tindel Toyota | "Tindel Toyota" |
| False | Honda | Valley View Auto Sales | "Valley View Auto Sales" |
+-----------------------+--------------+------------------------+--------------------------+
In this second example, against the same data, different search terms are used:
SELECT SEARCH((r.vehicle_make, r.dealership, s.src:dealership), 'Toyota Honda')
AS contains_toyota_honda, r.vehicle_make, r.dealership, s.src:dealership
FROM car_rentals r JOIN car_sales s
ON r.SALESPERSON =s.src:salesperson.name;
+-----------------------+--------------+------------------------+--------------------------+
| CONTAINS_TOYOTA_HONDA | VEHICLE_MAKE | DEALERSHIP | S.SRC:DEALERSHIP |
|-----------------------+--------------+------------------------+--------------------------|
| True | Toyota | Tindel Toyota | "Tindel Toyota" |
| True | Honda | Valley View Auto Sales | "Valley View Auto Sales" |
+-----------------------+--------------+------------------------+--------------------------+
Using an analyzer to adjust search behavior¶
The following examples show how to adjust the behavior of the SEARCH function by specifying a non-default analyzer: UNICODE_ANALYZER or NO_OP_ANALYZER.
The first example uses the NO_OP_ANALYZER to test whether the string 1.2.500 matches the exact contents
of the act_scene_line column for any row in the lines table. Two rows qualify for the search.
SELECT line_id, act_scene_line FROM lines
WHERE SEARCH(act_scene_line, '1.2.500', ANALYZER=>'NO_OP_ANALYZER');
+---------+----------------+
| LINE_ID | ACT_SCENE_LINE |
|---------+----------------|
| 91998 | 1.2.500 |
| 108464 | 1.2.500 |
+---------+----------------+
If you remove NO_OP_ANALYZER as an argument to the function for this example, the search returns a large number of rows. The default analyzer treats 1, 2, and 500 as distinct tokens; therefore, the function returns TRUE for all of the rows where 1, 2, or 500 exist (in any order or combination).
If you change this query to include only the prefix 1.2 for the second argument, the default analyzer returns TRUE, but the UNICODE_ANALYZER and NO_OP_ANALYZER both return FALSE. The default analyzer treats periods in these values as delimiters, but the Unicode analyzer does not.
The following two queries show another effect of using the UNICODE_ANALYZER instead of the default analyzer. The first query, using the UNICODE_ANALYZER, returns only one row. Note that the extra single quote in the second argument is there to escape the single quote for the apostrophe. See Single-quoted string constants.
SELECT DISTINCT(play)
FROM lines
WHERE SEARCH(play, 'love''s', ANALYZER=>'UNICODE_ANALYZER');
+----------------------+
| PLAY |
|----------------------|
| Love's Labour's Lost |
+----------------------+
The second query, using the default analyzer, returns four rows because the default analyzer treats the apostrophe
character as a delimiter. Any string that contains the letter “s” as a token qualifies for the search.
In this example, the function returns TRUE for every string that contains an “apostrophe s” ('s).
SELECT DISTINCT(play) FROM lines WHERE SEARCH(play, 'love''s');
+---------------------------+
| PLAY |
|---------------------------|
| All's Well That Ends Well |
| Love's Labour's Lost |
| A Midsummer Night's Dream |
| The Winter's Tale |
+---------------------------+
Examples of expected error cases¶
The following examples show queries that return expected syntax errors.
This example fails because 5 is not a supported data type for the search_string argument.
SELECT SEARCH(line, 5) FROM lines;
001045 (22023): SQL compilation error:
argument needs to be a string: '1'
This example fails because there is no column of a supported data type specified for the search_data argument.
SELECT SEARCH(line_id, 'dream') FROM lines;
001173 (22023): SQL compilation error: error line 1 at position 7: Expected non-empty set of columns supporting full-text search.
This example succeeds because there is a column of a supported data type specified for the search_data argument. The function
ignores the line_id column because it is not a supported data type
SELECT SEARCH((line_id, play), 'dream') FROM lines
ORDER BY play LIMIT 5;
+----------------------------------+
| SEARCH((LINE_ID, PLAY), 'DREAM') |
|----------------------------------|
| True |
| True |
| False |
| False |
| False |
+----------------------------------+
This example fails because multiple string literals are listed for the first argument, without parentheses, resulting in mismatched arguments:
SELECT SEARCH('docs@snowflake.com', 'careers@snowflake.com', '@');
001881 (42601): SQL compilation error: Expected 1 named argument(s), found 0
This example fails because multiple column names are listed for the first argument, without parentheses, resulting in too many arguments:
SELECT SEARCH(play,line,'king', ANALYZER=>'UNICODE_ANALYZER') FROM lines;
000939 (22023): SQL compilation error: error line 1 at position 7
too many arguments for function [SEARCH(LINES.PLAY, LINES.LINE, 'king', 'UNICODE_ANALYZER')] expected 3, got 4
This example fails because a column name is not accepted as the search string argument.
SELECT SEARCH(line, character) FROM lines;
001015 (22023): SQL compilation error:
argument 2 to function SEARCH needs to be constant, found 'LINES.CHARACTER'
Sample data for SEARCH¶
Some of the examples in this section query a table that contains text from Shakespeare’s plays.
Each line of text is stored in a single row of the table. Other columns identify the name of the play,
the name of the character, and so on. The lines table has the following structure:
DESCRIBE TABLE lines;
+----------------+---------------+--------+-------+-
| name | type | kind | null? |
|----------------+---------------+--------+-------+-
| LINE_ID | NUMBER(38,0) | COLUMN | Y |
| PLAY | VARCHAR(50) | COLUMN | Y |
| SPEECH_NUM | NUMBER(38,0) | COLUMN | Y |
| ACT_SCENE_LINE | VARCHAR(10) | COLUMN | Y |
| CHARACTER | VARCHAR(30) | COLUMN | Y |
| LINE | VARCHAR(2000) | COLUMN | Y |
+----------------+---------------+--------+-------+-
For example, a single line in this table looks like this:
SELECT * FROM lines
WHERE line_id=34230;
+---------+--------+------------+----------------+-----------+--------------------------------------------+
| LINE_ID | PLAY | SPEECH_NUM | ACT_SCENE_LINE | CHARACTER | LINE |
|---------+--------+------------+----------------+-----------+--------------------------------------------|
| 34230 | Hamlet | 19 | 3.1.64 | HAMLET | To be, or not to be, that is the question: |
+---------+--------+------------+----------------+-----------+--------------------------------------------+
If you want to run the examples in this section, create this table by running the following commands:
CREATE OR REPLACE TABLE lines(
line_id INT,
play VARCHAR(50),
speech_num INT,
act_scene_line VARCHAR(10),
character VARCHAR(30),
line VARCHAR(2000)
);
INSERT INTO lines VALUES
(4,'Henry IV Part 1',1,'1.1.1','KING HENRY IV','So shaken as we are, so wan with care,'),
(13,'Henry IV Part 1',1,'1.1.10','KING HENRY IV','Which, like the meteors of a troubled heaven,'),
(9526,'Henry VI Part 3',1,'1.1.1','WARWICK','I wonder how the king escaped our hands.'),
(12664,'All''s Well That Ends Well',1,'1.1.1','COUNTESS','In delivering my son from me, I bury a second husband.'),
(15742,'All''s Well That Ends Well',114,'5.3.378','KING','Your gentle hands lend us, and take our hearts.'),
(16448,'As You Like It',2,'2.3.6','ADAM','And wherefore are you gentle, strong and valiant?'),
(24055,'The Comedy of Errors',14,'5.1.41','AEMELIA','Be quiet, people. Wherefore throng you hither?'),
(28487,'Cymbeline',3,'1.1.10','First Gentleman','Is outward sorrow, though I think the king'),
(33522,'Hamlet',1,'2.2.1','KING CLAUDIUS','Welcome, dear Rosencrantz and Guildenstern!'),
(33556,'Hamlet',5,'2.2.35','KING CLAUDIUS','Thanks, Rosencrantz and gentle Guildenstern.'),
(33557,'Hamlet',6,'2.2.36','QUEEN GERTRUDE','Thanks, Guildenstern and gentle Rosencrantz:'),
(33776,'Hamlet',67,'2.2.241','HAMLET','Guildenstern? Ah, Rosencrantz! Good lads, how do ye both?'),
(34230,'Hamlet',19,'3.1.64','HAMLET','To be, or not to be, that is the question:'),
(35672,'Hamlet',7,'4.6.27','HORATIO','where I am. Rosencrantz and Guildenstern hold their'),
(36289,'Hamlet',14,'5.2.60','HORATIO','So Guildenstern and Rosencrantz go to''t.'),
(36640,'Hamlet',143,'5.2.389','First Ambassador','That Rosencrantz and Guildenstern are dead:'),
(43494,'King John',1,'1.1.1','KING JOHN','Now, say, Chatillon, what would France with us?'),
(43503,'King John',5,'1.1.10','CHATILLON','To this fair island and the territories,'),
(49031,'King Lear',1,'1.1.1','KENT','I thought the king had more affected the Duke of'),
(49040,'King Lear',4,'1.1.10','GLOUCESTER','so often blushed to acknowledge him, that now I am'),
(52797,'Love''s Labour''s Lost',1,'1.1.1','FERDINAND','Let fame, that all hunt after in their lives,'),
(55778,'Love''s Labour''s Lost',405,'5.2.971','ADRIANO DE ARMADO','Apollo. You that way: we this way.'),
(67000,'A Midsummer Night''s Dream',1,'1.1.1','THESEUS','Now, fair Hippolyta, our nuptial hour'),
(69296,'A Midsummer Night''s Dream',104,'5.1.428','PUCK','And Robin shall restore amends.'),
(75787,'Pericles',178,'1.0.21','LODOVICO','This king unto him took a fere,'),
(78407,'Richard II',1,'1.1.1','KING RICHARD II','Old John of Gaunt, time-honour''d Lancaster,'),
(91998,'The Tempest',108,'1.2.500','FERDINAND','Were I but where ''tis spoken.'),
(92454,'The Tempest',150,'2.1.343','ALONSO','Wherefore this ghastly looking?'),
(99330,'Troilus and Cressida',30,'1.1.102','AENEAS','How now, Prince Troilus! wherefore not afield?'),
(100109,'Troilus and Cressida',31,'2.1.53','ACHILLES','Why, how now, Ajax! wherefore do you thus? How now,'),
(108464,'The Winter''s Tale',106,'1.2.500','CAMILLO','As or by oath remove or counsel shake')
;