- 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>' ]
[ , 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')
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 use:
'blue' 'red' 'green'A lista de termos pode ser disjuntiva ou conjuntiva quando
ORouANDé definido para o argumento SEARCH_MODE. Entretanto, quando o'NO_OP_ANALYZER'é usado, a string de consulta é correspondida exatamente como está, sem tokenização e sem semântica disjuntiva ou conjuntiva.As pesquisas não diferenciam maiúsculas de minúsculas, exceto quando
'NO_OP_ANALYZER'é usado, então uma busca pelo termo'ONCE'na string'Once upon a time'retorna 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.
O analisador divide os termos de pesquisa (e o texto da coluna que está sendo pesquisada) em tokens. A semântica correspondente (disjuntiva, conjuntiva, frase ou exata) para tokens extraídos da string de pesquisa e tokens extraídos das colunas ou campos pesquisados depende do valor do valor do argumento SEARCH_MODE.
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+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': 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 string 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 string de consulta pareça conter vários tokens (por exemplo,'sky blue'), a coluna ou o campo deve ser exatamente igual à string 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.
SEARCH_MODE => { 'OR' | 'AND' | 'PHRASE' | 'EXACT' }A semântica usada pela pesquisa. Defina esse argumento como um dos seguintes valores:
'OR'- A função usa semântica disjuntiva. Há uma correspondência se qualquer dos tokens extraídos das colunas ou campos pesquisados corresponder a qualquer das tokens extraídas da string de pesquisa. Por exemplo, se o valor desearch_stringé'blue red green', a função retorna TRUE para uma linha que contémblueORredORgreenem alguma das colunas ou dos campos pesquisados.'AND'- A função usa semântica conjuntiva. 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. Os tokens correspondentes devem estar todos em uma coluna ou campo; eles não podem ser distribuídos em várias colunas ou campos. Por exemplo, se o valor desearch_stringfor'blue red green', a função retornará TRUE para uma linha que contenhablueANDredANDgreenem pelo menos uma das colunas ou campos pesquisados.'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¶
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:
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 Criação dos dados de amostra para 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');
O exemplo a seguir executa uma consulta que usa semântica conjuntiva para pesquisar nos dados de e-mails definindo o argumento SEARCH_MODE para 'AND' na primeira pesquisa, semântica de correspondência com frases na segunda pesquisa e semântica de correspondência exata na terceira pesquisa:
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 |
+-------------------------------+--------------------+---------------+--------------+
O exemplo a seguir executa uma consulta que usa semântica conjuntiva para pesquisar nos dados de e-mails definindo o argumento SEARCH_MODE para 'AND' na primeira pesquisa, semântica de correspondência com frases na segunda pesquisa e semântica de correspondência exata na terceira pesquisa:
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¶
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');
+---------+----------------+
| 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 em que 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 retorna TRUE, mas 'UNICODE_ANALYZER' e 'NO_OP_ANALYZER' retornam 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. 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');
+----------------------+
| 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'
Criação dos 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;
+----------------+---------------+--------+-------+-
| 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')
;