- Categorias:
Funções de cadeia de caracteres e binários (pesquisa de texto completo)
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 don’t specify one.
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>' ]
[ , SEARCH_MODE => { 'OR' | 'AND' | 'PHRASE' | 'EXACT' } ] )
Argumentos exigidos¶
search_dataOs 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.*)
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%')
EXCLUDE filtra nomes de coluna que não correspondem à coluna ou colunas especificadas. Por exemplo:
(* EXCLUDE col1) (* EXCLUDE (col1, col2))
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 tabelamytable:(mytable.* ILIKE 'col1%')
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');
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');
No entanto, você não pode especificar
*outable.*mais de uma vez para a função. No exemplo de junção anterior, você não pode especificarSEARCH((T1.*, T2.*), 'string'). Esta sintaxe retorna um erro.Parênteses são necessários para o argumento
search_dataquando*,table.*ou vários itens são listados. Por exemplo:SEARCH((col1, col2, col3), 'string') SEARCH((t1.*), 'string') SEARCH((*), 'string')
If parentheses aren’t used to separate multiple items, commas are parsed as separators between function arguments.
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 use:
'blue' 'red' 'green'The list of terms can be disjunctive or conjunctive when
ORorANDis set for the SEARCH_MODE argument. However, when the'NO_OP_ANALYZER'is used, the query string is matched exactly as it is, with no tokenization and no disjunctive or conjunctive semantics.Searches aren’t 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.Quando
ORouANDé definido para SEARCH_MODE Argumento, a ordem dos termos de pesquisa não importa em relação à sua presença nos dados pesquisados. QuandoPHRASEouEXACTé definido para SEARCH_MODE Argumento, a ordem dos termos de pesquisa deve corresponder exatamente aos dados pesquisados.
Argumentos opcionais¶
ANALYZER => 'analyzer_name'O nome do analisador de texto. O nome deve ser delimitado entre aspas simples.
The analyzer breaks the search terms (and the text from the column being searched) into tokens. The matching semantics (disjunctive, conjunctive, phrase, or exact) for tokens extracted from the search string and tokens extracted from the columns or fields being searched depends on the value of the SEARCH_MODE argument.
The analyzer tokenizes a string by breaking it where it finds certain delimiters. These delimiters aren’t included in the resulting tokens, and empty tokens aren’t extracted.
Este parâmetro aceita um dos seguintes valores:
'DEFAULT_ANALYZER'- Breaks text into tokens based on the following delimiters:Caractere
Código Unicode
Descrição
U+0020Espaço
[U+005BColchete esquerdo
]U+005DColchete direito
;U+003BPonto e vírgula
<U+003CSinal de menor que
>U+003ESinal de maior que
(U+0028Parêntese esquerdo
)U+0029Parêntese direito
{U+007BChave esquerda
}U+007DChave direita
|U+007CBarra vertical
!U+0021Ponto de exclamação
,U+002CVírgula
'U+0027Apóstrofo
"U+0022Aspa
*U+002AAsterisco
&U+0026E comercial
?U+003FPonto de interrogação
+U+002BSinal de mais
/U+002FBarra
:U+003ADois pontos
=U+003DSinal de igual
@U+0040No sinal
.U+002EPonto final
-U+002DHífen
$U+0024Símbolo de dólar
%U+0025Sinal de porcentagem
\U+005CBarra invertida
_U+005FSublinhado (traço subscrito)
\nU+000ANova linha (alimentação de linha)
\rU+000DRetorno de carro
\tU+0009Guia horizontal
'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 doesn’t. See 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'- 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'aren’t matches.
Para obter mais informações sobre o comportamento de diferentes analisadores, consulte Como os termos de pesquisa são tokenizados.
SEARCH_MODE => { 'OR' | 'AND' | 'PHRASE' | 'EXACT' }The semantics used by the search. Set this argument to one of the following values:
'OR'- The function uses disjunctive semantics. There is a match if any of the tokens extracted from the columns or fields being searched match any of the tokens extracted from the search string. For example, if thesearch_stringvalue is'blue red green', the function returns TRUE for a row that containsblueORredORgreenin any of the columns or fields being searched.'AND'- The function uses conjunctive semantics. There is a match if the tokens extracted from at least one of the columns or fields being searched matches all of the tokens extracted from the search string. The matching tokens must all be in one column or field; they can’t be spread across multiple columns or fields. For example, if thesearch_stringvalue is'blue red green', the function returns TRUE for a row that containsblueANDredANDgreenin at least one of the columns or fields being searched.'PHRASE'- A função usa semântica de correspondência de frase. Há uma correspondência se os tokens extraídos de pelo menos uma das colunas ou campos pesquisados corresponderem a todos os tokens extraídos da string de pesquisa, incluindo a ordem e a adjacência dos tokens.A semântica correspondente é a mesma semântica conjuntiva, exceto pelas seguintes diferenças:
A ordem dos tokens deve corresponder exatamente. Por exemplo, se o
search_stringO valor é'blue,red,green', a função retorna FALSE parared,green,blue.Nenhum token adicional pode ser acrescentado aos dados de pesquisa. Por exemplo, se o
search_stringO valor é'blue,red,green', a função retorna FALSE parablue,yellow,red,green.
'EXACT'- A função usa semântica de correspondência exata. Há uma correspondência se os tokens extraídos de pelo menos uma das colunas ou campos pesquisados corresponderem exatamente a todos os tokens extraídos da string de pesquisa, incluindo os delimitadores.As regras de correspondência são as mesmas da semântica de pesquisa de frase, exceto pelas seguintes diferenças:
As strings delimitadoras entre os tokens devem corresponder exatamente. Por exemplo, se o valor de
search_stringé'blue,red,green', a função retorna TRUE para uma linha que contémblue,red,greenem pelo menos uma das colunas ou campos pesquisados. A função retorna FALSE para variações comoblue|red|greenoublue, red, green.Quando um delimitador é o primeiro ou o último caractere no
search_stringvalor, o delimitador é tratado como um caractere para correspondência. Portanto, os delimitadores à esquerda e à direita do primeiro e do último delimitadores podem resultar em uma correspondência. Por exemplo, se o valor desearch_stringé'[blue]', a função retorna TRUE parafoo [blue] bar,[[blue]]e=[blue]., mas não para(blue)oufoo blue bar.
Para todos os modos de pesquisa, a string deve ser delimitada por um símbolo delimitador à esquerda e à direita. Por exemplo, se o valor de
search_stringé'blue,red,green', a função retorna TRUE para-blue,red,green;. A função retorna FALSE paradarkblue,red,greenoublue,red,greenish.Se você usar a
UNICODE_ANALYZER, a semântica de correspondência exata não é compatível. Você pode usar oDEFAULT_ANALYZERou oNO_OP_ANALYZERcom semântica de correspondência exata, mas geralmente essas semânticas de pesquisa são mais adequadas para oDEFAULT_ANALYZER.Uma pesquisa que usa semântica de correspondência exata com o
DEFAULT_ANALYZERse comporta de maneira diferente de uma pesquisa de igualdade ou de texto completo com oNO_OP_ANALYZERdas seguintes maneiras:Uma pesquisa de igualdade corresponde a linhas em que o valor da coluna é exatamente o mesmo que no predicado (incluindo maiúsculas e minúsculas), sem texto adicional em torno da ocorrência da string de pesquisa.
Uma pesquisa de texto completo com a
NO_OP_ANALYZERé semelhante a uma pesquisa de igualdade no fato de diferenciar maiúsculas de minúsculas e não permitir texto adicional.Uma pesquisa com semântica de correspondência exata com o
DEFAULT_ANALYZERtokeniza os valores da coluna. Ele permite tokens adicionais à esquerda e à direita da ocorrência da string de pesquisa, desde que estejam separados por delimitadores de token. A pesquisa não diferencia maiúsculas de minúsculas.
Padrão:
'OR'
Retornos¶
Retorna um BOOLEAN.
O valor é TRUE se os tokens de
search_stringcorresponderem aos tokens desearch_datacom base na semântica especificada no argumento SEARCH_MODE.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_datanão contiver dados desses tipos de dados. Quando o argumentosearch_datainclui 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);
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) |
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Exemplos¶
The following examples show different ways to use the SEARCH function, starting with simple usage and progressing to more complex use cases:
Pesquisa curinga em todas as colunas qualificadas em uma tabela
Como encontrar linhas que correspondem a várias sequências de pesquisa
Como encontrar linhas usando correspondência de frase e semântica de correspondência exata
Como usar um analisador para ajustar o comportamento da pesquisa
Para executar as consultas em vários exemplos, primeiro criar os dados de amostra para o SEARCH Função.
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 do primeiro e do segundo argumentos coincidem, já que a comparação não diferencia maiúsculas de minúsculas.
SELECT SEARCH('king','KING');
+-----------------------------+
| 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');
+-----------------------------+
| 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 porque o valor padrão do argumento SEARCH_MODE é 'OR'. (Para obter informações sobre a tabela usada aqui e em alguns exemplos subsequentes, consulte Creating the 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 |
+---------------------------------+---------------+
O exemplo a seguir é semelhante ao exemplo anterior, mas a semântica da pesquisa é conjuntiva porque o argumento SEARCH_MODE está definido como 'AND'. A função retorna FALSE porque apenas um dos termos de pesquisa (king) existe na coluna character. O termo queen não aparece nos dados de pesquisa.
SELECT SEARCH(character, 'king queen', SEARCH_MODE => 'AND'), character
FROM lines
WHERE line_id=4;
+-------------------------------------------------------+---------------+
| SEARCH(CHARACTER, 'KING QUEEN', SEARCH_MODE => 'AND') | CHARACTER |
|-------------------------------------------------------+---------------|
| False | KING HENRY IV |
+-------------------------------------------------------+---------------+
Pesquisa de cláusula WHERE em uma coluna¶
A consulta a seguir usa a função SEARCH para encontrar linhas que contenham a palavra wherefore na coluna line:
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? |
+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+
Pesquisa de cláusula WHERE em várias colunas¶
A consulta a seguir usa a função SEARCH para encontrar linhas que contenham 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;
+---------------------------+-----------------+
| 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. O resultado consiste nas linhas em que 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. O argumento SEARCH_MODE tem como padrão o endereço 'OR'.
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 |
+-----------------+-----------------+----------------------------------------------------+----------------+
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. Portanto, a função pesquisa nas colunas line e act_scene_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 |
+-----------------+-----------------+--------------------------------------------------+----------------+
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;
+-----------------+-----------------+----------------------------------------------------+----------------+
| 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. O argumento SEARCH_MODE tem como padrão o endereço 'OR'.
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 |
+--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------+
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. Portanto, a função pesquisa nas colunas line e act_scene_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 |
+--------+---------------------------+-----------------+--------------------------------------------------------+
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;
+--------+---------------------------+-----------------+--------------------------------------------------------+
| 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');
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');
+------+------+--------------+------+--------------+-------------+
| 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 |
+------+------+--------------+------+---------------+-------------+
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');
+------+------+--------------+
| 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 usa o argumento SEARCH_MODE para especificar a semântica conjuntiva que encontra uma correspondência quando os dois termos de pesquisa ocorrem juntos na mesma coluna. Para usar a semântica conjuntiva, defina o argumento SEARCH_MODE como 'AND'.
SELECT act_scene_line, character, line
FROM lines
WHERE SEARCH(line, 'Rosencrantz Guildenstern', SEARCH_MODE => 'AND')
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: |
+----------------+------------------+-----------------------------------------------------------+
Quando você usa a semântica conjuntiva, deve haver uma correspondência em ambos os termos de pesquisa na mesma coluna. Por exemplo, a consulta a seguir não retorna nenhum resultado porque os termos KING e Rosencrantz não aparecem na mesma coluna em nenhuma linha dos dados da pesquisa.
SELECT act_scene_line, character, line
FROM lines
WHERE SEARCH(line, 'KING Rosencrantz', SEARCH_MODE => 'AND')
AND act_scene_line IS NOT NULL;
+----------------+-----------+------+
| ACT_SCENE_LINE | CHARACTER | LINE |
|----------------+-----------+------|
+----------------+-----------+------+
Uma consulta semelhante que usa semântica disjuntiva (o padrão), definindo o argumento SEARCH_MODE como 'OR', encontra correspondências nos dados de pesquisa.
SELECT act_scene_line, character, line
FROM lines
WHERE SEARCH(line, 'KING Rosencrantz', SEARCH_MODE => 'OR')
AND act_scene_line IS NOT NULL;
+----------------+------------------+-----------------------------------------------------------+
| ACT_SCENE_LINE | CHARACTER | LINE |
|----------------+------------------+-----------------------------------------------------------|
| 1.1.1 | WARWICK | I wonder how the king escaped our hands. |
| 1.1.10 | First Gentleman | Is outward sorrow, though I think the king |
| 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: |
| 1.1.1 | KENT | I thought the king had more affected the Duke of |
| 1.0.21 | LODOVICO | This king unto him took a fere, |
+----------------+------------------+-----------------------------------------------------------+
Como encontrar linhas usando correspondência de frase e semântica de correspondência exata¶
Você pode usar correspondência de frase e correspondência exata para casos de uso semelhantes, mas ligeiramente diferentes:
Use a semântica de correspondência de frase quando as palavras e a ordem das palavras devem corresponder exatamente, mas pode haver diferenças nos delimitadores e no espaço entre as palavras. Para usar a semântica de correspondência de frase, defina o SEARCH_MODE Argumento para
'PHRASE'.Use semântica de correspondência exata quando as palavras, a ordem das palavras, os delimitadores entre as palavras e o espaço entre as palavras devem corresponder exatamente. Para usar uma semântica de correspondência exata, defina o SEARCH_MODE Argumento para
'EXACT'.
O exemplo a seguir usa semântica de correspondência de frase para encontrar uma correspondência exata de texto em uma string de texto mais longa, mas o texto de pesquisa tem delimitadores diferentes e espaços extras entre as palavras:
SELECT act_scene_line, character, line
FROM lines
WHERE SEARCH(line, 'Why - how now: Ajax!', SEARCH_MODE => 'PHRASE');
+----------------+-----------+-----------------------------------------------------+
| ACT_SCENE_LINE | CHARACTER | LINE |
|----------------+-----------+-----------------------------------------------------|
| 2.1.53 | ACHILLES | Why, how now, Ajax! wherefore do you thus? How now, |
+----------------+-----------+-----------------------------------------------------+
O exemplo a seguir é o mesmo que o exemplo anterior, exceto que ele usa semântica de correspondência exata para encontrar uma correspondência exata de texto em uma string de texto mais longa:
SELECT act_scene_line, character, line
FROM lines
WHERE SEARCH(line, 'Why, how now, Ajax!', SEARCH_MODE => 'EXACT');
+----------------+-----------+-----------------------------------------------------+
| ACT_SCENE_LINE | CHARACTER | LINE |
|----------------+-----------+-----------------------------------------------------|
| 2.1.53 | ACHILLES | Why, how now, Ajax! wherefore do you thus? How now, |
+----------------+-----------+-----------------------------------------------------+
Os casos de uso comuns para correspondência de frase e semântica de correspondência exata incluem localização de endereços de e-mail, URLsnúmeros de telefone, e. Para os próximos exemplos, crie uma tabela com uma linha de dados de amostra:
CREATE OR REPLACE TABLE phrase_exact_search_samples (
email VARCHAR,
url VARCHAR,
phone VARCHAR);
INSERT INTO phrase_exact_search_samples VALUES (
'john.robert.doe@mycompany.com',
'http://mycompany.com/product/id-12345.67',
'800-555-0100');
The following example runs a query that uses conjunctive semantics to search the email data by setting the SEARCH_MODE
argument to 'AND' in the first search, phrase-match semantics in the second search, and exact-match semantics
in the third search:
SELECT email AS search_data,
SEARCH(email, 'doe.john.robert@mycompany.com', SEARCH_MODE => 'AND') AS conjunctive_search,
SEARCH(email, 'doe.john.robert@mycompany.com', SEARCH_MODE => 'PHRASE') AS phrase_search,
SEARCH(email, 'doe.john.robert@mycompany.com', SEARCH_MODE => 'EXACT') AS exact_search
FROM phrase_exact_search_samples;
A saída mostra os seguintes resultados:
A pesquisa
ANDretorna TRUE mesmo que os termosjohn,robert``e ``doeestão em uma ordem diferente na string de pesquisa e nos dados de pesquisa.A pesquisa
PHRASEeEXACTretorna FALSE porque os termos de pesquisa não correspondem à ordem da string de pesquisa.
+-------------------------------+--------------------+---------------+--------------+
| SEARCH_DATA | CONJUNCTIVE_SEARCH | PHRASE_SEARCH | EXACT_SEARCH |
|-------------------------------+--------------------+---------------+--------------|
| john.robert.doe@mycompany.com | True | False | False |
+-------------------------------+--------------------+---------------+--------------+
The following example runs a query that uses conjunctive semantics to search the email data by setting
the SEARCH_MODE argument to 'AND' in the first search, phrase-match semantics in the second search, and exact-match
semantics in the third search:
SELECT email AS search_data,
SEARCH(email, 'john.doe@mycompany.com', SEARCH_MODE => 'AND') AS conjunctive_search,
SEARCH(email, 'john.doe@mycompany.com', SEARCH_MODE => 'PHRASE') AS phrase_search,
SEARCH(email, 'john.doe@mycompany.com', SEARCH_MODE => 'EXACT') AS exact_search
FROM phrase_exact_search_samples;
A saída mostra os seguintes resultados:
ANDpesquisa retorna TRUE mesmo que o token adicionalroberté intercalado nos dados de pesquisa.PHRASEeEXACTretorno de pesquisa FALSE porque essas semânticas de pesquisa não encontram uma correspondência quando tokens adicionais são alternados nos dados de pesquisa.
+-------------------------------+--------------------+---------------+--------------+
| SEARCH_DATA | CONJUNCTIVE_SEARCH | PHRASE_SEARCH | EXACT_SEARCH |
|-------------------------------+--------------------+---------------+--------------|
| john.robert.doe@mycompany.com | True | False | False |
+-------------------------------+--------------------+---------------+--------------+
O exemplo a seguir executa uma consulta que usa semântica de pesquisa de frase para pesquisar os dados de e-mail na primeira pesquisa e semântica de correspondência exata na segunda pesquisa:
SELECT email AS search_data,
SEARCH(email, 'john-robert-doe@mycompany.com', SEARCH_MODE => 'PHRASE') AS phrase_search,
SEARCH(email, 'john-robert-doe@mycompany.com', SEARCH_MODE => 'EXACT') AS exact_search
FROM phrase_exact_search_samples;
A saída mostra que o PHRASE pesquisa retorna TRUE mesmo que os delimitadores no endereço de e-mail na cadeia de pesquisa sejam hifens em vez de pontos entre john, robert``e ``doe. A pesquisa EXACT retorna FALSE porque, com semântica de correspondência exata, os delimitadores na string de pesquisa devem corresponder exatamente aos dados de pesquisa:
+-------------------------------+---------------+--------------+
| SEARCH_DATA | PHRASE_SEARCH | EXACT_SEARCH |
|-------------------------------+---------------+--------------|
| john.robert.doe@mycompany.com | True | False |
+-------------------------------+---------------+--------------+
O exemplo a seguir executa uma consulta que usa semântica de pesquisa de frase para pesquisar o URL dados na primeira pesquisa e semântica de correspondência exata na segunda pesquisa:
SELECT url AS search_data,
SEARCH(url, 'http://mycompany.com/product/id-12345_67', SEARCH_MODE => 'PHRASE') AS phrase_search,
SEARCH(url, 'http://mycompany.com/product/id-12345_67', SEARCH_MODE => 'EXACT') AS exact_search
FROM phrase_exact_search_samples;
A saída mostra que a pesquisa PHRASE retorna1 TRUE mesmo que o delimitador no URL na string de pesquisa seja um sublinhado em vez de um ponto final no ID do produto. O EXACT pesquisa retorna FALSE:
+------------------------------------------+---------------+--------------+
| SEARCH_DATA | PHRASE_SEARCH | EXACT_SEARCH |
|------------------------------------------+---------------+--------------|
| http://mycompany.com/product/id-12345.67 | True | False |
+------------------------------------------+---------------+--------------+
O exemplo a seguir executa uma consulta que usa semântica de pesquisa de frase para pesquisar os dados de número de telefone na primeira pesquisa e semântica de correspondência exata na segunda pesquisa:
SELECT phone AS search_data,
SEARCH(phone, '800.555.0100', SEARCH_MODE => 'PHRASE') AS phrase_search,
SEARCH(phone, '800.555.0100', SEARCH_MODE => 'EXACT') AS exact_search
FROM phrase_exact_search_samples;
A saída mostra que o PHRASE pesquisa retorna TRUE mesmo que os delimitadores no número de telefone na string de pesquisa sejam pontos em vez de hifens. O EXACT pesquisa retorna FALSE:
+--------------+---------------+--------------+
| SEARCH_DATA | PHRASE_SEARCH | EXACT_SEARCH |
|--------------+---------------+--------------|
| 800-555-0100 | True | False |
+--------------+---------------+--------------+
Os exemplos a seguir usam a função SEARCH em uma cláusula WHERE para consultar a tabela phrase_exact_search_samples. Primeiro, insira outra linha na tabela:
INSERT INTO phrase_exact_search_samples VALUES (
'jane.smith@mycompany.com',
'http://mycompany.com/product/id-89012.34',
'800-555-0199');
O exemplo a seguir procura uma correspondência exata do número de telefone 800-555-0100 nos dados da tabela:
SELECT *
FROM phrase_exact_search_samples
WHERE SEARCH(phone, '800-555-0100', SEARCH_MODE => 'EXACT');
+-------------------------------+------------------------------------------+--------------+
| EMAIL | URL | PHONE |
|-------------------------------+------------------------------------------+--------------|
| john.robert.doe@mycompany.com | http://mycompany.com/product/id-12345.67 | 800-555-0100 |
+-------------------------------+------------------------------------------+--------------+
O exemplo a seguir é o mesmo que o exemplo anterior, mas usa semântica disjuntiva em vez de semântica de correspondência exata, de modo que qualquer número de telefone que contenha 800 ou 555 é uma correspondência:
SELECT *
FROM phrase_exact_search_samples
WHERE SEARCH(phone, '800-555-0100', SEARCH_MODE => 'OR');
+-------------------------------+------------------------------------------+--------------+
| EMAIL | URL | PHONE |
|-------------------------------+------------------------------------------+--------------|
| john.robert.doe@mycompany.com | http://mycompany.com/product/id-12345.67 | 800-555-0100 |
| jane.smith@mycompany.com | http://mycompany.com/product/id-89012.34 | 800-555-0199 |
+-------------------------------+------------------------------------------+--------------+
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');
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;
+-----------------------+--------------+------------------------+--------------------------+
| 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;
+-----------------------+--------------+------------------------+--------------------------+
| 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¶
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 doesn’t.
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. The extra single quote in the second
argument is there to escape the single quote for the apostrophe. See Constantes de cadeias de caracteres entre aspas simples.
SELECT DISTINCT(play)
FROM lines
WHERE SEARCH(play, 'love''s', ANALYZER=>'UNICODE_ANALYZER');
+----------------------+
| 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');
+---------------------------+
| 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;
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;
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;
+----------------------------------+
| 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', '@');
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;
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;
001015 (22023): SQL compilation error:
argument 2 to function SEARCH needs to be constant, found 'LINES.CHARACTER'
Creating the sample data for 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;
+----------------+---------------+--------+-------+-
| 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;
+---------+--------+------------+----------------+-----------+--------------------------------------------+
| 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')
;