Categorias:: Funções de cadeia de caracteres e binários (pesquisa de texto completo)

SEARCH¶

Pesquisa dados de caractere (texto) em colunas especificadas de uma ou mais tabelas, incluindo campos nas colunas VARIANT, OBJECT e ARRAY. Um analisador de texto divide o texto em tokens, que são unidades discretas de texto, como palavras ou números. Um analisador padrão será aplicado se você não especificar um.

Para obter mais informações sobre como usar esta função, consulte Uso da pesquisa de texto completo.

Sintaxe¶

SEARCH( <search_data>, <search_string> [ , ANALYZER => '<analyzer_name>' ] )

Copy

Argumentos¶

search_data

Os dados que você deseja pesquisar, expressos como uma lista delimitada por vírgulas de literais de cadeia de caracteres, nomes de colunas ou caminhos para campos em colunas VARIANT. Os dados de pesquisa também podem ser uma única cadeia de caracteres literal, o que pode ser útil quando você estiver testando a função.

É possível especificar o caractere curinga (*), onde * se expande para todas as colunas qualificadas em todas as tabelas que estiverem no escopo da função. Colunas qualificadas são aquelas que possuem os tipos de dados VARCHAR (texto), VARIANT, ARRAY e OBJECT. Os dados VARIANT, ARRAY e OBJECT são convertidos em texto para pesquisa.

Ao passar um curinga para a função, você pode qualificar o curinga com o nome ou alias da tabela. Por exemplo, para passar todas as colunas da tabela nomeadas mytable, especifique o seguinte:

(mytable.*)

Copy

Também é possível usar as palavras-chave ILIKE e EXCLUDE para filtrar:

ILIKE filtra nomes de colunas que correspondem ao padrão especificado. Apenas um padrão é permitido. Por exemplo:
```
(* ILIKE 'col1%')
```
Copy
EXCLUDE filtra nomes de coluna que não correspondem à coluna ou colunas especificadas. Por exemplo:
```
(* EXCLUDE col1)

(* EXCLUDE (col1, col2))
```
Copy

Os qualificadores são válidos quando você usa essas palavras-chave. O exemplo a seguir usa a palavra-chave ILIKE para filtrar todas as colunas que correspondem ao padrão col1% na tabela mytable:

(mytable.* ILIKE 'col1%')

Copy

As palavras-chave ILIKE e EXCLUDE não podem ser combinadas em uma única chamada de função.

Para obter mais informações sobre as palavras-chave ILIKE e EXCLUDE, consulte a seção «Parâmetros» em SELECT.

É possível pesquisar colunas de mais de uma tabela quando várias tabelas estiverem no escopo, unindo tabelas ou usando o operador de conjuntos UNION. Para pesquisar todas as colunas na saída de uma junção ou consulta UNION, é possível usar o curinga não qualificado * da seguinte forma:

SELECT *
  FROM t AS T1
    JOIN t AS T2 USING (col1)
  WHERE SEARCH((*), 'string');

Copy

Para pesquisar colunas específicas ao unir tabelas, talvez seja necessário qualificar os nomes das colunas (por exemplo, table2.colname). Também é possível usar um curinga * qualificado da seguinte forma:

SELECT *
  FROM t AS T1
    JOIN t AS T2 USING (col1)
  WHERE SEARCH((T2.*), 'string');

Copy

Entretanto, observe que você não pode especificar * ou table.* mais de uma vez para a função. No exemplo de junção anterior, você não pôde especificar SEARCH((T1.*, T2.*), 'string'). Esta sintaxe retorna um erro.

Parênteses são necessários para o argumento search_data quando *, table.* ou vários itens são listados. Por exemplo:

SEARCH((col1, col2, col3), 'string')
SEARCH((t1.*), 'string')
SEARCH((*), 'string')

Copy

Se parênteses não forem usados para separar vários itens, vírgulas serão analisadas como separadores entre argumentos de função.

Veja também Exemplos de casos de erros esperados.

Também é possível pesquisar campos em dados VARIANT especificando o nome da coluna, dois pontos ou ponto e os subcampos separados por pontos. Por exemplo: colname:fieldname.subfieldname. Para obter mais informações sobre como especificar campos nessas colunas, consulte Como percorrer dados semiestruturados.

search_string

Uma cadeia de caracteres VARCHAR com um ou mais termos de pesquisa. Este argumento deve ser uma cadeia de caracteres literal; nomes de colunas não são compatíveis. Especifique um par de aspas simples em torno de toda a cadeia de caracteres. Não coloque aspas em torno de termos ou frases individuais. Por exemplo, usar:

'blue red green'

Não usar:

'blue' 'red' 'green'

A lista de termos é disjuntiva. Nesse caso, a pesquisa procuraria por linhas que contenham blue OR red OR green. Entretanto, quando o NO_OP_ANALYZER é usado, a cadeia de caracteres de consulta é correspondida exatamente como está, sem tokenização e sem semântica disjuntiva.

As pesquisas não diferenciam maiúsculas de minúsculas (exceto quando NO_OP_ANALYZER é usado), então uma busca pelo termo 'ONCE' na cadeia de caracteres 'Once upon a time' retorna TRUE.

A ordem dos termos de pesquisa não importa, em relação à sua presença nos dados pesquisados.

ANALYZER => 'analyzer_name'

Argumento opcional que especifica o nome do analisador de texto. O nome deve ser delimitado entre aspas simples.

O analisador divide os termos de pesquisa (e o texto da coluna que está sendo pesquisada) em tokens. Uma linha corresponde se algum dos tokens extraídos da cadeia de caracteres de pesquisa corresponder exatamente a um token extraído de qualquer uma das colunas ou campos pesquisados.

O analisador tokeniza uma cadeia de caracteres dividindo-a onde encontra certos delimitadores. Esses delimitadores não são incluídos nos tokens resultantes e os tokens vazios não são extraídos.

Este parâmetro aceita um dos seguintes valores:

DEFAULT_ANALYZER: Divide o texto em tokens com base nos seguintes delimitadores:

Caractere	Código Unicode	Descrição
	`U+0020`	Espaço
`[`	`U+005B`	Colchete esquerdo
`]`	`U+005D`	Colchete direito
`;`	`U+003B`	Ponto e vírgula
`<`	`U+003C`	Sinal de menor que
`>`	`U+003E`	Sinal de maior que
`(`	`U+0028`	Parêntese esquerdo
`)`	`U+0029`	Parêntese direito
`{`	`U+007B`	Chave esquerda
`}`	`U+007D`	Chave direita
`\|`	`U+007C`	Barra vertical
`!`	`U+0021`	Ponto de exclamação
`,`	`U+002C`	Vírgula
`'`	`U+0027`	Apóstrofo
`"`	`U+0022`	Aspa
`*`	`U+002A`	Asterisco
`&`	`U+0026`	E comercial
`?`	`U+003F`	Ponto de interrogação
`+`	`U+002B`	Sinal de mais
`/`	`U+002F`	Barra
`:`	`U+003A`	Dois pontos
`=`	`U+003D`	Sinal de igual
`@`	`U+0040`	No sinal
`.`	`U+002E`	Ponto final
`-`	`U+002D`	Hífen
`$`	`U+0024`	Símbolo de dólar
`%`	`U+0025`	Sinal de porcentagem
`\`	`U+005C`	Barra invertida
`_`	`U+005F`	Sublinhado (traço subscrito)
`\n`	`U+000A`	Nova linha (alimentação de linha)
`\r`	`U+000D`	Retorno de carro
`\t`	`U+0009`	Guia horizontal

UNICODE_ANALYZER: Tokeniza com base em regras de segmentação Unicode que tratam espaços e certos caracteres de pontuação como delimitadores. Essas regras internas são projetadas para pesquisas em linguagem natural (em muitos idiomas diferentes). Por exemplo, o analisador padrão trata pontos em endereços IP e apóstrofos em contrações como delimitadores, mas o analisador Unicode não. Consulte Como usar um analisador para ajustar o comportamento da pesquisa.

Para obter mais informações sobre o algoritmo de segmentação de texto Unicode, consulte https://unicode.org/reports/tr29/.
NO_OP_ANALYZER: Não tokeniza nem os dados nem a cadeia de caracteres de consulta. Um termo de pesquisa deve corresponder exatamente ao texto completo em uma coluna ou campo, incluindo diferenciação de maiúsculas e minúsculas; caso contrário, a função SEARCH retornará FALSE. Mesmo que a cadeia de caracteres de consulta pareça conter vários tokens (por exemplo, 'sky blue'), a coluna ou o campo deve ser exatamente igual à cadeia de caracteres de consulta inteira. Neste caso, apenas 'sky blue' é uma correspondência; 'sky' e 'blue' não são correspondências.

Para obter mais informações sobre o comportamento de diferentes analisadores, consulte Como os termos de pesquisa são tokenizados.

Retornos¶

Retorna um BOOLEAN.

O valor é TRUE se algum token search_string for encontrado em search_data.
Retorna NULL se qualquer um desses argumentos for NULL.
Caso contrário, retorna FALSE.

Notas de uso¶

A função SEARCH opera somente em dados VARCHAR, VARIANT, ARRAY e OBJECT. A função retorna um erro se o argumento search_data não contiver dados desses tipos de dados. Quando o argumento search_data inclui dados de tipos de dados compatíveis e incompatíveis, a função pesquisa os dados dos tipos de dados compatíveis e ignora silenciosamente os dados dos tipos de dados incompatíveis. Para exemplos, consulte Exemplos de casos de erro esperados.
É possível adicionar uma otimização de pesquisa FULL_TEXT em colunas que são destino de chamadas de função SEARCH usando um comando ALTER TABLE. Por exemplo:
```
ALTER TABLE lines ADD SEARCH OPTIMIZATION
  ON FULL_TEXT(play, character, line);
```
Copy
Para obter mais informações, consulte Como habilitar a otimização de pesquisa FULL_TEXT.

Como os termos de pesquisa são tokenizados¶

A tabela a seguir mostra alguns exemplos de como os termos de pesquisa de entrada são divididos em tokens, o que depende das regras aplicadas pelo analisador usado. Na tabela, vírgulas indicam onde os tokens são divididos (se forem).

Termo(s) de pesquisa	Tokens: DEFAULT_ANALYZER	Tokens: UNICODE_ANALYZER	NO_OP_ANALYZER (não dividido)
`10.210.158.44`	`10, 210, 158, 44`	`10.210.158.44`	`10.210.158.44`
`192.0.2.0/24`	`192, 0, 2, 24`	`192.0.2.0, 24`	`192.0.2.0/24`
`high-tech`	`high, tech`	`high, tech`	`high-tech`
`Bob's Burgers`	`bob, s, burgers`	`bob's, burgers`	`Bob's Burgers`
`Three spaces`	`three, spaces`	`three, spaces`	`Three spaces`
`docs@snowflake.com`	`docs, snowflake, com`	`docs, snowflake.com`	`docs@snowflake.com`
`/opt/homebrew/README.md`	`opt, homebrew, readme, md`	`opt, homebrew, readme.md`	`/opt/homebrew/README.md`

Exemplos¶

Os exemplos a seguir mostram diferentes maneiras de usar a função SEARCH, começando com o uso simples e progredindo para casos de uso mais complexos.

Correspondência com um literal¶

O exemplo mais simples da função SEARCH é um teste para TRUE ou FALSE em uma literal de cadeia de caracteres. O primeiro exemplo retorna TRUE porque os literais para o primeiro e o segundo argumentos correspondem, já que a comparação não diferencia maiúsculas de minúsculas.

SELECT SEARCH('king','KING');

Copy

+-----------------------------+
| SEARCH('KING','KING')       |
|-----------------------------|
| True                        |
+-----------------------------+

O segundo exemplo retorna FALSE porque o token 32 não aparece no literal 5.1.33 especificado para o primeiro argumento.

SELECT SEARCH('5.1.33','32');

Copy

+-----------------------------+
| SEARCH('5.1.33','32')       |
|-----------------------------|
| False                       |
+-----------------------------+

Correspondência com uma referência de coluna¶

Este exemplo usa uma coluna em uma tabela como o primeiro argumento. A função retorna TRUE porque um dos termos de pesquisa (king) existe na coluna character. A lista de termos é disjuntiva. (Para obter informações sobre a tabela usada aqui e em alguns exemplos subsequentes, consulte Dados de amostra para SEARCH.)

SELECT SEARCH(character, 'king queen'),character
  FROM lines
  WHERE line_id=4;

Copy

+--------------------------------------+---------------+
| SEARCH(CHARACTER,'KING QUEEN') | CHARACTER           |
|--------------------------------------+---------------|
| True                                 | KING HENRY IV |
+--------------------------------------+---------------+

Pesquisa de cláusula WHERE em uma coluna¶

A consulta a seguir usa a função SEARCH para encontrar linhas com a palavra “wherefore” na coluna line:

SELECT *
  FROM lines
  WHERE SEARCH(line, 'wherefore')
  ORDER BY character LIMIT 5;

Copy

+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+
| 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?                     |
+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+

Pesquisa de cláusula WHERE em várias colunas¶

A consulta a seguir usa a função SEARCH para encontrar linhas com a palavra “king” na coluna play, na coluna character ou em ambas as colunas. Parênteses são necessários para o primeiro argumento.

SELECT play, character
  FROM lines
  WHERE SEARCH((play, character), 'king')
  ORDER BY play, character LIMIT 10;

Copy

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

Pesquisa curinga em todas as colunas qualificadas em uma tabela¶

É possível usar o caractere * (ou table.*) como o primeiro argumento para a função SEARCH, conforme mostrado neste exemplo. A pesquisa opera em todas as colunas qualificadas na tabela que você está selecionando, que neste caso é a tabela lines.

A tabela lines tem quatro colunas que possuem tipos de dados compatíveis com a função de pesquisa. Observe que o resultado consiste em linhas onde “king” aparece em uma ou mais das quatro colunas pesquisadas. Para uma dessas colunas, act_scene_line, a função não encontra correspondências, mas as outras três colunas têm correspondências.

SELECT play, character, line, act_scene_line
  FROM lines
  WHERE SEARCH((lines.*), 'king')
  ORDER BY act_scene_line LIMIT 10;

Copy

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

Você também pode usar as palavras-chave ILIKE e EXCLUDE para filtrar. Para obter mais informações sobre essas palavras-chave, consulte SELECT.

Esta pesquisa usa a palavra-chave ILIKE para pesquisar apenas em colunas que terminam com a cadeia de caracteres line.

SELECT play, character, line, act_scene_line
  FROM lines
  WHERE SEARCH((lines.* ILIKE '%line'), 'king')
  ORDER BY act_scene_line LIMIT 10;

Copy

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

Esta pesquisa usa a palavra-chave EXCLUDE para que a função não pesquise os dados na coluna character.

SELECT play, character, line, act_scene_line
  FROM lines
  WHERE SEARCH((lines.* EXCLUDE character), 'king')
  ORDER BY act_scene_line LIMIT 10;

Copy

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

Pesquisa curinga em uma lista SELECT¶

É possível usar o caractere * (ou table.*) em uma lista SELECT, conforme mostrado nestes exemplos.

A pesquisa a seguir opera em todas as colunas qualificadas na tabela que você está selecionando, que neste caso é a tabela lines. A pesquisa retorna True quando “king” aparece em uma ou mais das quatro colunas pesquisadas.

SELECT SEARCH((*), 'king') result, *
  FROM lines
  ORDER BY act_scene_line LIMIT 10;

Copy

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

Você também pode usar as palavras-chave ILIKE e EXCLUDE para filtrar. Para obter mais informações sobre essas palavras-chave, consulte SELECT.

Esta pesquisa usa a palavra-chave ILIKE para pesquisar apenas em colunas que terminam com a cadeia de caracteres line.

SELECT SEARCH(* ILIKE '%line', 'king') result, play, character, line
  FROM lines
  ORDER BY act_scene_line LIMIT 10;

Copy

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

Esta pesquisa usa a palavra-chave EXCLUDE para que a função não pesquise os dados nas colunas play ou line.

SELECT SEARCH(* EXCLUDE (play, line), 'king') result, play, character, line
  FROM lines
  ORDER BY act_scene_line LIMIT 10;

Copy

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

Pesquisa curinga em colunas qualificadas em tabelas unidas¶

Este exemplo usa duas pequenas tabelas com informações sobre modelos de carros. A tabela t1 tem duas colunas de caractere e a tabela t2 tem três. Você pode criar e carregar as tabelas da seguinte maneira:

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');

Copy

Os resultados das duas consultas a seguir são diferentes, dada a pesquisa em t1.* e t2.*. Apenas duas colunas de t1 se qualificam para a pesquisa, mas três colunas de t2 se qualificam.

SELECT * FROM t1 JOIN t2 USING(col1)
  WHERE SEARCH((t1.*),'s all4');

Copy

+------+------+--------------+------+--------------+-------------+
| 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');

Copy

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

Pesquisa curinga na saída de uma subconsulta UNION¶

O exemplo a seguir usa as mesmas duas tabelas do exemplo anterior. Nesse caso, a pesquisa é aplicada a todas as colunas qualificadas de t3, que é a tabela que resulta da subconsulta. A subconsulta calcula a UNION das três primeiras colunas em t1 e t2 (cinco linhas). A pesquisa retorna duas linhas correspondentes do resultado de UNION.

SELECT *
  FROM (
    SELECT col1, col2, col3 FROM t1
    UNION
    SELECT col1, col2, col3 FROM t2
    ) AS T3
  WHERE SEARCH((T3.*),'s');

Copy

+------+------+--------------+
| COL1 | COL2 | COL3         |
|------+------+--------------|
|    2 | Mini | Cooper S     |
|    4 | Mini | Countryman S |
+------+------+--------------+

Como encontrar linhas que correspondem a várias sequências de pesquisa¶

O exemplo a seguir mostra como usar SEARCH duas vezes na mesma consulta, para que ambas as cadeias de caracteres de pesquisa tenham que retornar TRUE para que uma linha se qualifique para o resultado.

SELECT act_scene_line, character, line
  FROM lines
  WHERE SEARCH(line, 'Rosencrantz')
    AND SEARCH(line, 'Guildenstern')
    AND act_scene_line IS NOT NULL;

Copy

+----------------+------------------+-----------------------------------------------------------+
| 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:               |
+----------------+------------------+-----------------------------------------------------------+

Pesquisa de dados VARIANT e VARCHAR em uma junção¶

O exemplo a seguir mostra uma junção de duas tabelas, car_rentals e car_sales, com a pesquisa aplicada às colunas em ambas as tabelas. A tabela car_sales contém dados VARIANT. A tabela car_sales e seus dados são descritos em Consulta de dados semiestruturados. As instruções SQL a seguir criam a tabela car_rentals e inserem dados nela:

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');

Copy

Executar a consulta:

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;

Copy

+-----------------------+--------------+------------------------+--------------------------+
| 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" |
+-----------------------+--------------+------------------------+--------------------------+

Neste segundo exemplo, em relação aos mesmos dados, são usados termos de pesquisa diferentes:

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;

Copy

+-----------------------+--------------+------------------------+--------------------------+
| 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" |
+-----------------------+--------------+------------------------+--------------------------+

Como usar um analisador para ajustar o comportamento da pesquisa¶

Os exemplos a seguir mostram como ajustar o comportamento da função SEARCH especificando um analisador não padrão: UNICODE_ANALYZER ou NO_OP_ANALYZER.

O primeiro exemplo usa NO_OP_ANALYZER para testar se a cadeia de caracteres 1.2.500 corresponde ao conteúdo exato da coluna act_scene_line para qualquer linha na tabela lines. Duas linhas se qualificam para a pesquisa.

SELECT line_id, act_scene_line FROM lines
  WHERE SEARCH(act_scene_line, '1.2.500', ANALYZER=>'NO_OP_ANALYZER');

Copy

+---------+----------------+
| LINE_ID | ACT_SCENE_LINE |
|---------+----------------|
|   91998 | 1.2.500        |
|  108464 | 1.2.500        |
+---------+----------------+

Se você remover NO_OP_ANALYZER como um argumento da função para este exemplo, a pesquisa retornará um grande número de linhas. O analisador padrão trata 1, 2 e 500 como tokens distintos; portanto, a função retorna TRUE para todas as linhas onde 1, 2 ou 500 existem (em qualquer ordem ou combinação).

Se você alterar esta consulta para incluir apenas o prefixo 1.2 para o segundo argumento, o analisador padrão retornará TRUE, mas UNICODE_ANALYZER e NO_OP_ANALYZER retornarão FALSE. O analisador padrão trata os pontos nesses valores como delimitadores, mas o analisador Unicode não.

As duas consultas a seguir mostram outro efeito do uso de UNICODE_ANALYZER em vez do analisador padrão. A primeira consulta, usando o UNICODE_ANALYZER, retorna apenas uma linha. Observe que as aspas simples extras no segundo argumento estão lá para escapar das aspas simples do apóstrofo. Consulte Constantes de cadeias de caracteres entre aspas simples.

SELECT DISTINCT(play)
  FROM lines
  WHERE SEARCH(play, 'love''s', ANALYZER=>'UNICODE_ANALYZER');

Copy

+----------------------+
| PLAY                 |
|----------------------|
| Love's Labour's Lost |
+----------------------+

A segunda consulta, usando o analisador padrão, retorna quatro linhas porque o analisador padrão trata o caractere de apóstrofo como um delimitador. Qualquer cadeia de caracteres que contenha a letra “s” como token se qualifica para a pesquisa. Neste exemplo, a função retorna TRUE para cada cadeia de caracteres com um “apóstrofo s” ('s).

SELECT DISTINCT(play) FROM lines WHERE SEARCH(play, 'love''s');

Copy

+---------------------------+
| PLAY                      |
|---------------------------|
| All's Well That Ends Well |
| Love's Labour's Lost      |
| A Midsummer Night's Dream |
| The Winter's Tale         |
+---------------------------+

Exemplos de casos de erro esperados¶

Os exemplos a seguir mostram consultas que retornam erros de sintaxe esperados.

Este exemplo falha porque 5 não é um tipo de dados compatível para o argumento search_string.

SELECT SEARCH(line, 5) FROM lines;

Copy

001045 (22023): SQL compilation error:
argument needs to be a string: '1'

Este exemplo falha porque não há nenhuma coluna de um tipo de dados compatível especificado para o argumento search_data.

SELECT SEARCH(line_id, 'dream') FROM lines;

Copy

001173 (22023): SQL compilation error: error line 1 at position 7: Expected non-empty set of columns supporting full-text search.

Este exemplo é bem-sucedido porque há uma coluna de um tipo de dados compatível especificado para o argumento search_data. A função ignora a coluna line_id porque não é um tipo de dados compatível.

SELECT SEARCH((line_id, play), 'dream') FROM lines
  ORDER BY play LIMIT 5;

Copy

+----------------------------------+
| SEARCH((LINE_ID, PLAY), 'DREAM') |
|----------------------------------|
| True                             |
| True                             |
| False                            |
| False                            |
| False                            |
+----------------------------------+

Este exemplo falha porque vários literais de cadeia de caracteres são listados para o primeiro argumento, sem parênteses, resultando em argumentos incompatíveis:

SELECT SEARCH('docs@snowflake.com', 'careers@snowflake.com', '@');

Copy

001881 (42601): SQL compilation error: Expected 1 named argument(s), found 0

Este exemplo falha porque vários nomes de colunas são listados para o primeiro argumento, sem parênteses, resultando em muitos argumentos:

SELECT SEARCH(play,line,'king', ANALYZER=>'UNICODE_ANALYZER') FROM lines;

Copy

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

Este exemplo falha porque um nome de coluna não é aceito como argumento de cadeia de caracteres de pesquisa.

SELECT SEARCH(line, character) FROM lines;

Copy

001015 (22023): SQL compilation error:
argument 2 to function SEARCH needs to be constant, found 'LINES.CHARACTER'

Dados de amostra para SEARCH¶

Alguns dos exemplos nesta seção consultam uma tabela com texto das peças de Shakespeare. Cada linha de texto é armazenada em uma única linha da tabela. Outras colunas identificam o nome da peça, o nome do caractere e assim por diante. A tabela lines tem a seguinte estrutura:

DESCRIBE TABLE lines;

Copy

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

Por exemplo, uma única linha nesta tabela se parece com isto:

SELECT * FROM lines
  WHERE line_id=34230;

Copy

+---------+--------+------------+----------------+-----------+--------------------------------------------+
| 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: |
+---------+--------+------------+----------------+-----------+--------------------------------------------+

Se você quiser executar os exemplos nesta seção, crie esta tabela executando os seguintes comandos:

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')
  ;

Copy