Catégories :: Fonctions de chaîne et fonctions binaires (Recherche en texte intégral)

SEARCH¶

Recherche des données de caractères (texte) dans des colonnes spécifiées à partir d’une ou plusieurs tables, y compris dans les champs des colonnes VARIANT, OBJECT et ARRAY. Un analyseur de texte divise le texte en jetons, qui sont des unités de texte discrètes, telles que des mots ou des nombres. Un analyseur par défaut est appliqué si vous n’en spécifiez pas.

Pour plus d’informations sur l’utilisation de ces fonctions, voir Utilisation de la recherche en texte intégral.

Syntaxe¶

SEARCH( <search_data>, '<search_string>'
  [ , ANALYZER => '<analyzer_name>' ]
  [ , SEARCH_MODE => { 'OR' | 'AND' | 'PHRASE' | 'EXACT' } ] )

Arguments obligatoires¶

search_data

Les données que vous souhaitez rechercher, exprimées sous la forme d’une liste délimitée par des virgules de littéraux de chaîne, de noms de colonnes ou chemins vers les champs dans les colonnes VARIANT. Les données de recherche peuvent également être une chaîne littérale unique, ce qui peut être utile lorsque vous testez la fonction.

Vous pouvez spécifier le caractère générique (*), où * s’étend à toutes les colonnes éligibles dans toutes les tables concernées par la fonction. Les colonnes éligibles sont celles qui ont les types de données VARCHAR (texte), VARIANT, ARRAY et OBJECT. Les données VARIANT, ARRAY et OBJECT sont converties en texte pour la recherche.

Lorsque vous transmettez un caractère générique à la fonction, vous pouvez qualifier le caractère générique avec le nom ou l’alias de la table. Par exemple, pour transmettre toutes les colonnes de la table nommée mytable, précisez les éléments suivants :

(mytable.*)

Vous pouvez également utiliser les mots-clés ILIKE et EXCLUDE pour le filtrage :

ILIKE filtre les noms de colonnes qui correspondent au motif spécifié. Un seul motif est autorisé. Par exemple :
```
(* ILIKE 'col1%')
```
EXCLUDE filtre les noms de colonnes qui ne correspondent pas à la / aux colonnes spécifiées. Par exemple :
```
(* EXCLUDE col1)

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

Les qualificatifs sont valides lorsque vous utilisez ces mots-clés. L’exemple suivant utilise le mot-clé ILIKE pour filtrer toutes les colonnes qui correspondent au motif col1% dans la table mytable :

(mytable.* ILIKE 'col1%')

Les mots-clés ILIKE et EXCLUDE ne peuvent pas être combinés dans un seul appel de fonction.

Pour plus d’informations sur les mots-clés ILIKE et EXCLUDE, voir la section « Paramètres » dans SELECT.

Vous pouvez rechercher des colonnes dans plusieurs tables lorsque plusieurs tables sont concernées en joignant des tables ou en utilisant l’opérateur d’ensemble UNION. Pour rechercher toutes les colonnes dans la sortie d’une jointure ou d’une requête UNION, vous pouvez utiliser le caractère générique * non qualifié comme suit :

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

Pour rechercher des colonnes spécifiques lors de la jointure de tables, vous devrez peut-être qualifier les noms de colonnes (par exemple, table2.colname). Vous pouvez également faire appel à un caractère générique * qualifié comme suit :

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

Toutefois, vous ne pouvez pas spécifier * ou table.* plus d’une fois pour la fonction. Dans l’exemple de jointure précédent, vous ne pouvez pas spécifier SEARCH((T1.*, T2.*), 'string'). Cette syntaxe renvoie une erreur.

Les parenthèses sont obligatoires pour l’argument search_data quand *, table.* ou plusieurs éléments sont listés. Par exemple :

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

Si les parenthèses ne sont pas utilisées pour séparer plusieurs éléments, les virgules sont analysées comme des séparateurs entre les arguments de fonction.

Voir également Exemples de cas d’erreur prévus.

Vous pouvez rechercher des champs dans les données VARIANT en spécifiant le nom de la colonne, deux points ou un point et les sous-champs séparés par des points. Par exemple : colname:fieldname.subfieldname. Pour plus d’informations sur la spécification des champs dans ces colonnes, voir Parcours de données semi-structurées.

'search_string'

Une chaîne VARCHAR contenant un ou plusieurs termes de recherche. Cet argument doit être une chaîne littérale ; les noms de colonnes ne sont pas pris en charge. Spécifiez une paire de guillemets simples autour de la chaîne entière. Ne mettez pas de guillemets autour de phrases ou termes individuels. Par exemple, utilisez :

'blue red green'

N’utilisez pas :

'blue' 'red' 'green'

La liste des termes peut être disjonctive ou conjonctive lorsque OR ou AND est défini pour l’argument SEARCH_MODE. Cependant, lorsque 'NO_OP_ANALYZER' est utilisé, la chaîne de requête correspond exactement telle qu’elle est, sans tokenisation ni sémantique disjonctive ou conjonctive.

Les recherches ne sont pas sensibles à la casse, sauf lorsque 'NO_OP_ANALYZER' est utilisé, donc une recherche du terme 'ONCE' par rapport à la chaîne 'Once upon a time' renvoie TRUE.

Lorsque OR ou AND est défini pour l’argument SEARCH_MODE, l’ordre des termes de recherche n’a pas d’importance en ce qui concerne leur présence dans les données recherchées. Lorsque PHRASE ou EXACT est défini pour l’argument SEARCH_MODE, l’ordre des termes de recherche doit correspondre exactement aux données recherchées.

Arguments facultatifs¶

ANALYZER => 'analyzer_name'

Le nom de l’analyseur de texte. Le nom doit être entre guillemets simples.

L’analyseur divise les termes de recherche (et le texte de la colonne recherchée) en jetons. La sémantique de correspondance (disjonctive, conjonctive, phrastique ou exacte) pour les jetons extraits de la chaîne de recherche et les jetons extraits des colonnes ou des champs recherchés dépend de la valeur de l’argument SEARCH_MODE.

L’analyseur tokenise une chaîne en la cassant là où il trouve certains délimiteurs. Ces délimiteurs ne sont pas inclus dans les jetons résultants et les jetons vides ne sont pas extraits.

Ce paramètre accepte une des valeurs suivantes :

'DEFAULT_ANALYZER' - divise le texte en jetons en fonction des délimiteurs suivants :


Caractère	Code Unicode	Description
	`U+0020`	Espace
`[`	`U+005B`	Crochet gauche
`]`	`U+005D`	Crochet droit
`;`	`U+003B`	Point-virgule
`<`	`U+003C`	Signe inférieur à
`>`	`U+003E`	Signe supérieur à
`(`	`U+0028`	Parenthèse gauche
`)`	`U+0029`	Parenthèse droite
`{`	`U+007B`	Accolade gauche
`}`	`U+007D`	Accolade droite
`\|`	`U+007C`	Barre verticale
`!`	`U+0021`	Point d’exclamation
`,`	`U+002C`	Virgule
`'`	`U+0027`	Apostrophe
`"`	`U+0022`	Guillemets
`*`	`U+002A`	Astérisque
`&`	`U+0026`	Esperluette
`?`	`U+003F`	Point d’interrogation
`+`	`U+002B`	Signe plus
`/`	`U+002F`	Barre oblique
`:`	`U+003A`	Deux-points
`=`	`U+003D`	Signe égal
`@`	`U+0040`	Arobase
`.`	`U+002E`	Point (point final)
`-`	`U+002D`	Trait d’union
`$`	`U+0024`	Symbole du dollar
`%`	`U+0025`	Signe de pourcentage
`\`	`U+005C`	Barre oblique inverse
`_`	`U+005F`	Trait de soulignement (ligne basse)
`\n`	`U+000A`	Nouvelle ligne (saut de ligne)
`\r`	`U+000D`	Retour chariot
`\t`	`U+0009`	Onglet horizontal

'UNICODE_ANALYZER' - tokenise en fonction des règles de segmentation Unicode qui traitent les espaces et certains caractères de ponctuation comme des délimiteurs. Ces règles internes sont conçues pour les recherches en langage naturel dans de nombreuses langues différentes. Par exemple, l’analyseur par défaut traite les périodes dans les adresses IP et les apostrophes dans les contractions comme délimiteurs, mais l’analyseur Unicode ne le fait pas. Voir Utiliser un analyseur pour ajuster le comportement de recherche.

Pour plus d’informations sur l’algorithme de segmentation de texte Unicode, consultez https://unicode.org/reports/tr29/.
'NO_OP_ANALYZER' - ne tokenise ni les données ni la chaîne de requête. Un terme de recherche doit correspondre exactement au texte intégral d’une colonne ou d’un champ, y compris la sensibilité à la casse ; sinon, la fonction SEARCH renvoie FALSE. Même si la chaîne de requête semble contenir plusieurs jetons — par exemple, 'sky blue' — la colonne ou le champ doit être exactement égal à la chaîne de requête entière. Dans ce cas, seulement 'sky blue' est une correspondance ; 'sky' et 'blue' ne sont pas des correspondances.

Pour plus d’informations sur le comportement des différents analyseurs, voir Comment les termes de recherche sont tokenisés.

SEARCH_MODE => { 'OR' | 'AND' | 'PHRASE' | 'EXACT' }

La sémantique utilisée par la recherche. Définissez cet argument sur l’une des valeurs suivantes :

'OR' - La fonction utilise la sémantique disjonctive. Il y a une correspondance si l’un ou l’autre des jetons extraits des colonnes ou des champs recherchés correspond à l’un ou l’autre des jetons extraits de la chaîne de recherche. Par exemple, si la valeur search_string est 'blue red green', la fonction renvoie TRUE pour une ligne contenant blue OR red OR green dans l’une des colonnes ou l’un des champs recherchés.
'AND' - La fonction utilise la sémantique conjonctive. Il y a correspondance si les jetons extraits d’au moins une des colonnes ou un des champs recherchés correspondent à tous les jetons extraits de la chaîne de recherche. Les jetons correspondants doivent tous se trouver dans une seule colonne ou un seul champ ; ils ne peuvent pas être répartis dans plusieurs colonnes ou champs. Par exemple, si la valeur search_string est 'blue red green', la fonction renvoie TRUE pour une ligne contenant blue AND red AND green dans au moins l’une des colonnes ou l’un des champs recherchés.
'PHRASE' - La fonction utilise la sémantique de correspondance des phrases. Il y a correspondance si les jetons extraits d’au moins une des colonnes ou un des champs recherchés correspondent à tous les jetons extraits de la chaîne de recherche, y compris l’ordre et l’adéquation des jetons.

La sémantique de correspondance est la même que la sémantique conjonctive, à l’exception des différences suivantes :
- L’ordre des jetons doit correspondre exactement. Par exemple, si la valeur search_string est 'blue,red,green', la fonction renvoie FALSE pour red,green,blue.
- Aucun jeton supplémentaire ne peut être intercalé dans les données de recherche. Par exemple, si la valeur search_string est 'blue,red,green', la fonction renvoie FALSE pour blue,yellow,red,green.
'EXACT' - La fonction utilise la sémantique de correspondance exacte. Il y a correspondance si les jetons extraits d’au moins une des colonnes ou un des champs recherchés correspondent exactement à tous les jetons extraits de la chaîne de recherche, y compris les délimiteurs.

Les règles de correspondance sont les mêmes que pour la sémantique de recherche de phrases, à l’exception des différences suivantes :
- Les chaînes de délimiteurs entre les jetons doivent correspondre exactement. Par exemple, si la valeur search_string est 'blue,red,green', la fonction renvoie TRUE pour une ligne contenant blue,red,green dans au moins l’une des colonnes ou l’un des champs recherchés. La fonction renvoie FALSE pour les variations telles que blue|red| green ou blue, red, green.
- Lorsqu’un délimiteur est le premier ou le dernier caractère de la valeur search_string, le délimiteur est traité comme un caractère pour la correspondance. Par conséquent, les délimiteurs à gauche et à droite du premier et du dernier délimiteur peuvent entraîner une correspondance. Par exemple, si la valeur search_string est '[blue]', la fonction renvoie TRUE pour foo [blue] bar, [[blue]] et =[blue]., mais pas pour (blue) ou foo blue bar.
Pour tous les modes de recherche, la chaîne doit être délimitée par un symbole délimiteur à gauche et à droite. Par exemple, si la valeur search_string est 'blue,red,green', la fonction renvoie TRUE pour -blue,red,green;. La fonction renvoie FALSE pour darkblue,red,green ou blue,red,greenish.

Si vous utilisez le UNICODE_ANALYZER, la sémantique de correspondance exacte n’est pas prise en charge. Vous pouvez utiliser DEFAULT_ANALYZER ou NO_OP_ANALYZER avec une sémantique de correspondance exacte, mais généralement ces sémantiques de recherche conviennent mieux à DEFAULT_ANALYZER.

Une recherche qui utilise la sémantique de correspondance exacte avec DEFAULT_ANALYZER se comporte différemment d’une recherche d’égalité ou d’une recherche de texte intégral avec NO_OP_ANALYZER des manières suivantes :
- Une recherche d’égalité correspond aux lignes où la valeur de la colonne est exactement la même que dans le prédicat (y compris la casse), sans texte supplémentaire entourant l’occurrence de la chaîne de recherche.
- Une recherche en texte intégral avec NO_OP_ANALYZER est similaire à une recherche d’égalité dans la mesure où il est sensible à la casse et n’autorise pas de texte supplémentaire.
- Une recherche avec une sémantique de correspondance exacte avec DEFAULT_ANALYZER tokenise les valeurs de colonne. Il autorise des jetons supplémentaires à gauche et à droite de l’occurrence de la chaîne de recherche, à condition qu’ils soient séparés par des délimiteurs de jetons. La recherche est insensible à la casse.

Par défaut : 'OR'

Renvoie¶

Renvoie une valeur BOOLEAN :

La valeur est TRUE si les jetons search_string correspondent aux jetons search_data sur la base de la sémantique spécifiée dans l’argument SEARCH_MODE.
Renvoie NULL si l’un de ces arguments est NULL.
Sinon, renvoie FALSE.

Notes sur l’utilisation¶

La fonction SEARCH fonctionne uniquement sur les données VARCHAR, VARIANT, ARRAY et OBJECT. La fonction renvoie une erreur si l’argument search_data ne contient pas de données de ces types de données. Lorsque l’argument search_data inclut des données des types de données pris en charge et des types de données non pris en charge, la fonction recherche les données des types de données pris en charge et ignore silencieusement les données des types de données non pris en charge. Pour des exemples, voir Exemples de cas d’erreur prévus.
Vous pouvez ajouter une optimisation FULL_TEXT de la recherche sur les colonnes qui sont la cible d’appels de fonction SEARCH à l’aide d’une commande ALTER TABLE. Par exemple :
```
ALTER TABLE lines ADD SEARCH OPTIMIZATION
  ON FULL_TEXT(play, character, line);
```
Pour plus d’informations, voir activer l’optimisation de la recherche FULL_TEXT.

Comment les termes de recherche sont tokenisés¶

La table suivante montre quelques exemples de la manière dont les termes de recherche d’entrée sont divisés en jetons, ce qui dépend des règles appliquées par l’analyseur utilisé. Dans le tableau, les virgules indiquent où les jetons sont divisés (si c’est le cas).


Terme(s) de recherche	Jetons : DEFAULT_ANALYZER	Jetons : UNICODE_ANALYZER	NO_OP_ANALYZER (non divisé)
`10.210.158.44`	`10`, `210`, `158`, `44`	`10.210.158.44`	`10.210.158.44`
`192.0.2.0/24`	`192`, `0`, `2`, `24`	`192.0.2.0`, `24`	`192.0.2.0/24`
`high-tech`	`high`, `tech`	`high`, `tech`	`high-tech`
`Bob's Burgers`	`bob`, `s`, `burgers`	`bob's`, `burgers`	`Bob's Burgers`
`Three spaces`	`three`, `spaces`	`three`, `spaces`	`Three spaces`
`docs@snowflake.com`	`docs`, `snowflake`, `com`	`docs`, `snowflake.com`	`docs@snowflake.com`
`/opt/homebrew/README.md`	`opt`, `homebrew`, `readme`, `md`	`opt`, `homebrew`, `readme.md`	`/opt/homebrew/README.md`

Exemples¶

Les exemples suivants montrent différentes manières d’utiliser la fonction SEARCH, en commençant par une utilisation simple et en progressant vers des cas d’utilisation plus complexes :

Correspondance avec un littéral
Correspondance avec une référence de colonne
Recherche de clause WHERE sur une colonne
Recherche de clause WHERE sur plusieurs colonnes
Recherche générique sur toutes les colonnes éligibles dans une table
Recherche générique dans une liste SELECT
Recherche générique sur les colonnes éligibles dans les tables jointes
Recherche générique sur la sortie d’une sous-requête UNION
Recherche de lignes correspondant à plusieurs chaînes de recherche
Recherche de lignes à l’aide de la sémantique de correspondance de phrases et de correspondance exacte
Recherche de données VARIANT et VARCHAR dans une jointure
Utiliser un analyseur pour ajuster le comportement de recherche
Exemples de cas d’erreur prévus

Pour exécuter les requêtes de plusieurs exemples, commencez par créer les données d’échantillon pour la fonction SEARCH.

Correspondance avec un littéral¶

L’exemple le plus simple de la fonction SEARCH est un test pour TRUE ou FALSE sur une chaîne littérale. Le premier exemple renvoie TRUE parce que les littéraux des premier et deuxième arguments correspondent, étant donné que la comparaison n’est pas sensible à la casse.

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

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

Le deuxième exemple renvoie FALSE parce que le jeton 32 n’apparaît pas dans le littéral 5.1.33 spécifié pour le premier argument.

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

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

Correspondance avec une référence de colonne¶

Cet exemple utilise une colonne d’une table comme premier argument. La fonction renvoie TRUE parce que l’un des termes de recherche (king) existe dans la colonne character. La liste des termes est disjonctive car la valeur par défaut de l’argument SEARCH_MODE est 'OR'. (Pour plus d’informations sur la table utilisée ici et dans certains exemples ultérieurs, voir Création des données d’échantillon pour SEARCH.)

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

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

L’exemple suivant est similaire à l’exemple précédent, mais la sémantique de recherche est conjonctive car l’argument SEARCH_MODE est défini sur 'AND'. La fonction renvoie FALSE car un seul des termes recherchés (king) existe dans la colonne character. Le terme queen n’apparaît pas dans les données de recherche.

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

Recherche de clause WHERE sur une colonne¶

La requête suivante utilise la fonction SEARCH pour trouver les lignes qui contiennent le mot wherefore dans la colonne 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?                     |
+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+

Recherche de clause WHERE sur plusieurs colonnes¶

La requête suivante utilise la fonction SEARCH pour trouver les lignes qui contiennent le mot king dans la colonne play, la colonne character ou les deux colonnes. Les parenthèses sont obligatoires pour le premier argument.

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

+---------------------------+-----------------+
| PLAY                      | CHARACTER       |
|---------------------------+-----------------|
| All's Well That Ends Well | KING            |
| Hamlet                    | KING CLAUDIUS   |
| Hamlet                    | KING CLAUDIUS   |
| Henry IV Part 1           | KING HENRY IV   |
| Henry IV Part 1           | KING HENRY IV   |
| King John                 | CHATILLON       |
| King John                 | KING JOHN       |
| King Lear                 | GLOUCESTER      |
| King Lear                 | KENT            |
| Richard II                | KING RICHARD II |
+---------------------------+-----------------+

Recherche générique sur toutes les colonnes éligibles dans une table¶

Vous pouvez utiliser le caractère * (ou table.*) comme premier argument de la fonction SEARCH, comme illustré dans cet exemple. La recherche s’effectue sur toutes les colonnes éligibles dans le tableau que vous sélectionnez, qui dans ce cas est la table lines.

La table lines comporte quatre colonnes dont les types de données sont pris en charge par la fonction de recherche. Le résultat est constitué de lignes où king apparaît dans une ou plusieurs des quatre colonnes recherchées. Pour l’une de ces colonnes, act_scene_line, la fonction ne trouve aucune correspondance, mais les trois autres colonnes ont toutes des correspondances. L’argument SEARCH_MODE est défini par défaut sur '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         |
+-----------------+-----------------+----------------------------------------------------+----------------+

Vous pouvez également utiliser les mots-clés ILIKE et EXCLUDE pour le filtrage. Pour plus d’informations sur ces mots-clés, voir SELECT.

Cette recherche utilise le mot-clé ILIKE pour rechercher uniquement dans les colonnes qui se terminent par la chaîne line. La fonction effectue donc une recherche dans les colonnes line et 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         |
+-----------------+-----------------+--------------------------------------------------+----------------+

Cette recherche utilise le mot-clé EXCLUDE pour que la fonction ne recherche pas les données dans la colonne 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         |
+-----------------+-----------------+----------------------------------------------------+----------------+

Recherche générique dans une liste SELECT¶

Vous pouvez utiliser le caractère * (ou table.*) dans une liste SELECT, comme le montrent ces exemples.

La recherche suivante s’effectue sur toutes les colonnes éligibles dans le tableau que vous sélectionnez, qui dans ce cas est la table lines. La recherche renvoie TRUE lorsque king apparaît dans une ou plusieurs des quatre colonnes recherchées. L’argument SEARCH_MODE est défini par défaut sur '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             |
+--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------+

Vous pouvez également utiliser les mots-clés ILIKE et EXCLUDE pour le filtrage. Pour plus d’informations sur ces mots-clés, voir SELECT.

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

Cette recherche utilise le mot-clé EXCLUDE pour que la fonction ne recherche pas les données dans les colonnes 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             |
+--------+---------------------------+-----------------+--------------------------------------------------------+

Recherche générique sur les colonnes éligibles dans les tables jointes¶

Cet exemple utilise deux petites tables contenant des informations sur les modèles de voitures. La table t1 a deux colonnes de caractères et une table t2 en a trois. Vous pouvez créer et charger les tables comme suit :

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

Les résultats des deux requêtes suivantes diffèrent, étant donné la recherche sur t1.* et t2.*. Seulement deux colonnes de t1 sont admissibles à la recherche, contre trois colonnes pour t2.

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

Recherche générique sur la sortie d’une sous-requête UNION¶

L’exemple suivant utilise les deux mêmes tables que l’exemple précédent. Dans ce cas, la recherche est appliquée à toutes les colonnes éligibles de t3, qui est la table qui résulte de la sous-requête. La sous-requête calcule la valeur UNION des trois premières colonnes de t1 et t2 (cinq lignes). La recherche renvoie deux lignes correspondantes du résultat 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 |
+------+------+--------------+

Recherche de lignes correspondant à plusieurs chaînes de recherche¶

L’exemple suivant utilise l’argument SEARCH_MODE pour spécifier une sémantique conjonctive qui trouve une correspondance lorsque les deux termes de recherche apparaissent ensemble dans la même colonne. Pour utiliser la sémantique conjonctive, vous devez donner à l’argument SEARCH_MODE la valeur '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:               |
+----------------+------------------+-----------------------------------------------------------+

Lorsque vous utilisez la sémantique conjonctive, les deux termes de recherche doivent correspondre dans la même colonne. Par exemple, la requête suivante ne renvoie aucun résultat car les termes KING et Rosencrantz n’apparaissent dans la même colonne sur aucune ligne des données de recherche.

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

Une requête similaire qui utilise la sémantique disjonctive (par défaut) en définissant l’argument SEARCH_MODE sur 'OR' trouve des correspondances dans les données de recherche.

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

Recherche de lignes à l’aide de la sémantique de correspondance de phrases et de correspondance exacte¶

Vous pouvez utiliser la sémantique de correspondance de phrases et de correspondance exacte pour des cas d’utilisation similaires mais légèrement différents :

Utilisez la sémantique de correspondance de phrases lorsque les mots et leur ordre doivent correspondre exactement, mais qu’il peut y avoir des différences dans les délimiteurs et l’espace entre les mots. Pour utiliser la sémantique de correspondance de phrases, définissez l’argument SEARCH_MODE sur 'PHRASE'.
Utilisez la sémantique de correspondance exacte lorsque les mots, l’ordre des mots, les délimiteurs entre les mots et l’espace entre les mots doivent correspondre exactement. Pour utiliser la sémantique de correspondance exacte, définissez l’argument SEARCH_MODE sur 'EXACT'.

L’exemple suivant utilise la sémantique de correspondance de phrases pour trouver une correspondance exacte de texte dans une chaîne de texte plus longue, mais le texte de recherche comporte des délimiteurs différents et des espaces supplémentaires entre les mots :

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

L’exemple suivant est le même que l’exemple précédent, sauf qu’il utilise la sémantique de correspondance exacte pour trouver une correspondance exacte de texte dans une chaîne de texte plus longue :

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

Les cas d’utilisation courants de la sémantique de correspondance de phrases et de correspondance exacte incluent la recherche d’adresses e-mail, d’URLs et de numéros de téléphone. Pour les exemples suivants, créez une table avec une ligne de données d’échantillon :

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

L’exemple suivant exécute une requête qui utilise la sémantique conjonctive pour rechercher les données des e-mails en définissant l’argument SEARCH_MODE sur 'AND' dans la première recherche, la sémantique de correspondance de phrases dans la deuxième recherche, et la sémantique de correspondance exacte dans la troisième recherche :

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;

La sortie affiche les résultats suivants :

La recherche AND renvoie TRUE même si les termes john, robert et doe sont dans un ordre différent dans la chaîne de recherche et les données de recherche.
Les recherches PHRASE et EXACT renvoient FALSE car les termes de recherche ne correspondent pas à l’ordre de la chaîne de recherche.

+-------------------------------+--------------------+---------------+--------------+
| SEARCH_DATA                   | CONJUNCTIVE_SEARCH | PHRASE_SEARCH | EXACT_SEARCH |
|-------------------------------+--------------------+---------------+--------------|
| john.robert.doe@mycompany.com | True               | False         | False        |
+-------------------------------+--------------------+---------------+--------------+

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;

La sortie affiche les résultats suivants :

La recherche AND renvoie TRUE même si le jeton supplémentaire robert est intercalé dans les données de recherche.
Les recherches PHRASE et EXACT renvoient FALSE car cette sémantique de recherche ne trouve pas de correspondance lorsque des jetons supplémentaires sont intercalés dans les données de recherche.

+-------------------------------+--------------------+---------------+--------------+
| SEARCH_DATA                   | CONJUNCTIVE_SEARCH | PHRASE_SEARCH | EXACT_SEARCH |
|-------------------------------+--------------------+---------------+--------------|
| john.robert.doe@mycompany.com | True               | False         | False        |
+-------------------------------+--------------------+---------------+--------------+

L’exemple suivant exécute une requête qui utilise la sémantique de recherche de phrases pour rechercher les données d’e-mails dans la première recherche et la sémantique de correspondance exacte dans la deuxième recherche :

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;

La sortie montre que la recherche PHRASE renvoie TRUE même si les délimiteurs de l’adresse e-mail dans la chaîne de recherche sont des traits d’union au lieu de points entre john, robert et doe. La recherche EXACT renvoie FALSE car, avec une sémantique de correspondance exacte, les délimiteurs de la chaîne de recherche doivent correspondre exactement aux données de recherche :

+-------------------------------+---------------+--------------+
| SEARCH_DATA                   | PHRASE_SEARCH | EXACT_SEARCH |
|-------------------------------+---------------+--------------|
| john.robert.doe@mycompany.com | True          | False        |
+-------------------------------+---------------+--------------+

L’exemple suivant exécute une requête qui utilise la sémantique de recherche de phrases pour rechercher les données d’URL dans la première recherche et la sémantique de correspondance exacte dans la deuxième recherche :

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;

La sortie montre que la recherche PHRASE renvoie TRUE même si le délimiteur dans l’URL de la chaîne de recherche est un tiret bas au lieu d’un point dans l’ID de produit. La recherche EXACT renvoie FALSE :

+------------------------------------------+---------------+--------------+
| SEARCH_DATA                              | PHRASE_SEARCH | EXACT_SEARCH |
|------------------------------------------+---------------+--------------|
| http://mycompany.com/product/id-12345.67 | True          | False        |
+------------------------------------------+---------------+--------------+

L’exemple suivant exécute une requête qui utilise la sémantique de recherche de phrases pour rechercher les données de numéro de téléphone lors de la première recherche et la sémantique de correspondance exacte dans la deuxième recherche :

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;

La sortie montre que la recherche PHRASE renvoie TRUE même si les délimiteurs du numéro de téléphone dans la chaîne de recherche sont des points au lieu de traits d’union. La recherche EXACT renvoie FALSE :

+--------------+---------------+--------------+
| SEARCH_DATA  | PHRASE_SEARCH | EXACT_SEARCH |
|--------------+---------------+--------------|
| 800-555-0100 | True          | False        |
+--------------+---------------+--------------+

Les exemples suivants utilisent la fonction SEARCH dans une clause WHERE pour interroger la table phrase_exact_search_samples. Tout d’abord, insérez une autre ligne dans la table :

INSERT INTO phrase_exact_search_samples VALUES (
  'jane.smith@mycompany.com',
  'http://mycompany.com/product/id-89012.34',
  '800-555-0199');

L’exemple suivant recherche une correspondance exacte du numéro de téléphone 800-555-0100 dans les données de la table :

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

L’exemple suivant est le même que l’exemple précédent, mais il utilise une sémantique disjonctive au lieu d’une sémantique de correspondance exacte, de sorte que tout numéro de téléphone contenant un 800 ou 555 est une correspondance :

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

Recherche de données VARIANT et VARCHAR dans une jointure¶

L’exemple suivant montre une jointure de deux tables, car_rentals et car_sales, avec la recherche appliquée aux colonnes des deux tables. La table car_sales contient des données VARIANT. La table car_sales et ses données sont décrites sous Interrogation de données semi-structurées. Les instructions SQL suivantes créent la table car_rentals et y insèrent des données :

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

Exécutez la requête :

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

Dans ce deuxième exemple, sur les mêmes données, différents termes de recherche sont utilisés :

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

Utiliser un analyseur pour ajuster le comportement de recherche¶

Les exemples suivants montrent comment ajuster le comportement de la fonction SEARCH en spécifiant un analyseur autre que celui par défaut : 'UNICODE_ANALYZER' ou 'NO_OP_ANALYZER'.

Le premier exemple utilise 'NO_OP_ANALYZER' pour tester si la chaîne 1.2.500 correspond exactement au contenu de la colonne act_scene_line pour n’importe quelle ligne dans la table lines. Deux lignes sont éligibles à la recherche.

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

Si vous supprimez 'NO_OP_ANALYZER' en tant qu’argument de la fonction pour cet exemple, la recherche renvoie un grand nombre de lignes. L’analyseur par défaut traite 1, 2 et 500 comme des jetons distincts ; par conséquent, la fonction renvoie TRUE pour toutes les lignes où 1, 2 ou 500 existent dans n’importe quel ordre ou combinaison.

Si vous modifiez cette requête pour inclure uniquement le préfixe 1.2 pour le deuxième argument, l’analyseur par défaut renvoie TRUE, mais 'UNICODE_ANALYZER' et 'NO_OP_ANALYZER' renvoient tous les deux FALSE. L’analyseur par défaut traite les périodes dans ces valeurs comme des délimiteurs, mais l’analyseur Unicode ne le fait pas.

Les deux requêtes suivantes montrent un autre effet de l’utilisation de 'UNICODE_ANALYZER' au lieu de l’analyseur par défaut. La première requête, utilisant 'UNICODE_ANALYZER', ne renvoie qu’une seule ligne. Le guillemet simple supplémentaire dans le deuxième argument est là pour échapper au guillemet simple pour l’apostrophe. Voir Constantes de chaîne entre guillemets simples.

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

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

La deuxième requête, utilisant l’analyseur par défaut, renvoie quatre lignes car l’analyseur par défaut traite le caractère apostrophe comme un délimiteur. Toute chaîne contenant la lettre « s » comme jeton est éligible à la recherche. Dans cet exemple, la fonction renvoie TRUE pour chaque chaîne contenant une « apostrophe s » ('s).

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

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

Exemples de cas d’erreur prévus¶

Les exemples suivants montrent des requêtes qui renvoient des erreurs de syntaxe attendues.

Cet exemple échoue parce que 5 n’est pas un type de données pris en charge pour l’argument search_string.

SELECT SEARCH(line, 5) FROM lines;

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

Cet exemple échoue car aucune colonne d’un type de données pris en charge n’est spécifiée pour l’argument 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.

Cet exemple réussit car il existe une colonne d’un type de données pris en charge spécifié pour l’argument search_data. La fonction ignore la colonne line_id car ce n’est pas un type de données pris en charge

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

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

Cet exemple échoue car plusieurs littéraux de chaîne sont listés pour le premier argument, sans parenthèses, ce qui entraîne des arguments incompatibles :

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

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

Cet exemple échoue car plusieurs noms de colonnes sont listés pour le premier argument, sans parenthèses, ce qui entraîne trop d’arguments :

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

000939 (22023): SQL compilation error: error line 1 at position 7
too many arguments for function [SEARCH(LINES.PLAY, LINES.LINE, 'king', 'UNICODE_ANALYZER')] expected 3, got 4

Cet exemple échoue car un nom de colonne n’est pas accepté comme argument de chaîne de recherche.

SELECT SEARCH(line, character) FROM lines;

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

Création des données d’échantillon pour SEARCH¶

Certains des exemples de cette section interrogent une table contenant du texte provenant de pièces de Shakespeare. Chaque ligne de texte est stockée dans une seule ligne de la table. D’autres colonnes identifient le nom de la pièce, le nom du personnage, etc. La table lines a la structure suivante :

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

Par exemple, une seule ligne dans cette table ressemble à ceci :

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

Si vous souhaitez exécuter les exemples de cette section, créez cette table en exécutant les commandes suivantes :

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