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

Copy

Arguments¶

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.*)

Copy

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%')
```
Copy
EXCLUDE filtre les noms de colonnes qui ne correspondent pas à la / aux colonnes spécifiées. Par exemple :
```
(* EXCLUDE col1)

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

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

Copy

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

Copy

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

Copy

Notez cependant que 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 pouviez 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')

Copy

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 termes ou d’expressions individuels. Par exemple, utilisez :

'blue red green'

N’utilisez pas :

'blue' 'red' 'green'

La liste des termes est disjonctive. Dans ce cas, la recherche rechercherait des lignes contenant blue OR red OR green. 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.

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.

L’ordre des termes de recherche n’a pas d’importance en ce qui concerne leur présence dans les données recherchées.

ANALYZER => 'analyzer_name'

Argument facultatif qui spécifie 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. Une ligne correspond si l’un des jetons extraits de la chaîne de recherche correspond exactement à un jeton extrait de l’une des colonnes ou des champs recherchés.

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.

Renvoie¶

Renvoie un BOOLEAN.

La valeur est TRUE si des jetons search_string sont trouvés dans search_data.
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);
```
Copy
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¶

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

Copy

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

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

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

Copy

+-----------------------------+
| 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. (Pour plus d’informations sur la table utilisée ici et dans certains exemples ultérieurs, voir Exemple de données pour SEARCH.)

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

Copy

+--------------------------------------+---------------+
| SEARCH(CHARACTER,'KING QUEEN') | CHARACTER           |
|--------------------------------------+---------------|
| True                                 | 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 « pourquoi » dans la colonne line :

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

Copy

+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+
| LINE_ID | PLAY                 | SPEECH_NUM | ACT_SCENE_LINE | CHARACTER | LINE                                                |
|---------+----------------------+------------+----------------+-----------+-----------------------------------------------------|
|  100109 | Troilus and Cressida |         31 | 2.1.53         | ACHILLES  | Why, how now, Ajax! wherefore do you thus? How now, |
|   16448 | As You Like It       |          2 | 2.3.6          | ADAM      | And wherefore are you gentle, strong and valiant?   |
|   24055 | The Comedy of Errors |         14 | 5.1.41         | AEMELIA   | Be quiet, people. Wherefore throng you hither?      |
|   99330 | Troilus and Cressida |         30 | 1.1.102        | AENEAS    | How now, Prince Troilus! wherefore not afield?      |
|   92454 | The Tempest          |        150 | 2.1.343        | ALONSO    | Wherefore this ghastly looking?                     |
+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+

Recherche de clause WHERE sur plusieurs colonnes¶

La requête suivante utilise la fonction SEARCH pour trouver les lignes qui contiennent le mot « roi » 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;

Copy

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

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. Notez que le résultat est constitué de lignes où « roi » 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.

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

Copy

+-----------------+-----------------+----------------------------------------------------+----------------+
| PLAY            | CHARACTER       | LINE                                               | ACT_SCENE_LINE |
|-----------------+-----------------+----------------------------------------------------+----------------|
| Pericles        | LODOVICO        | This king unto him took a fere,                    | 1.0.21         |
| Richard II      | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster,        | 1.1.1          |
| Henry VI Part 3 | WARWICK         | I wonder how the king escaped our hands.           | 1.1.1          |
| King John       | KING JOHN       | Now, say, Chatillon, what would France with us?    | 1.1.1          |
| King Lear       | KENT            | I thought the king had more affected the Duke of   | 1.1.1          |
| Henry IV Part 1 | KING HENRY IV   | So shaken as we are, so wan with care,             | 1.1.1          |
| Henry IV Part 1 | KING HENRY IV   | Which, like the meteors of a troubled heaven,      | 1.1.10         |
| King Lear       | GLOUCESTER      | so often blushed to acknowledge him, that now I am | 1.1.10         |
| Cymbeline       | First Gentleman | Is outward sorrow, though I think the king         | 1.1.10         |
| King John       | CHATILLON       | To this fair island and the territories,           | 1.1.10         |
+-----------------+-----------------+----------------------------------------------------+----------------+

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.

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

Copy

+-----------------+-----------------+--------------------------------------------------+----------------+
| PLAY            | CHARACTER       | LINE                                             | ACT_SCENE_LINE |
|-----------------+-----------------+--------------------------------------------------+----------------|
| Pericles        | LODOVICO        | This king unto him took a fere,                  | 1.0.21         |
| Henry VI Part 3 | WARWICK         | I wonder how the king escaped our hands.         | 1.1.1          |
| King Lear       | KENT            | I thought the king had more affected the Duke of | 1.1.1          |
| Cymbeline       | First Gentleman | Is outward sorrow, though I think the king       | 1.1.10         |
+-----------------+-----------------+--------------------------------------------------+----------------+

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;

Copy

+-----------------+-----------------+----------------------------------------------------+----------------+
| PLAY            | CHARACTER       | LINE                                               | ACT_SCENE_LINE |
|-----------------+-----------------+----------------------------------------------------+----------------|
| Pericles        | LODOVICO        | This king unto him took a fere,                    | 1.0.21         |
| Henry VI Part 3 | WARWICK         | I wonder how the king escaped our hands.           | 1.1.1          |
| King John       | KING JOHN       | Now, say, Chatillon, what would France with us?    | 1.1.1          |
| King Lear       | KENT            | I thought the king had more affected the Duke of   | 1.1.1          |
| Cymbeline       | First Gentleman | Is outward sorrow, though I think the king         | 1.1.10         |
| King Lear       | GLOUCESTER      | so often blushed to acknowledge him, that now I am | 1.1.10         |
| King John       | CHATILLON       | To this fair island and the territories,           | 1.1.10         |
+-----------------+-----------------+----------------------------------------------------+----------------+

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 « roi » apparaît dans une ou plusieurs des quatre colonnes recherchées.

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

Copy

+--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------+
| RESULT | LINE_ID | PLAY                      | SPEECH_NUM | ACT_SCENE_LINE | CHARACTER       | LINE                                                   |
|--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------|
| True   |   75787 | Pericles                  |        178 | 1.0.21         | LODOVICO        | This king unto him took a fere,                        |
| True   |   43494 | King John                 |          1 | 1.1.1          | KING JOHN       | Now, say, Chatillon, what would France with us?        |
| True   |   49031 | King Lear                 |          1 | 1.1.1          | KENT            | I thought the king had more affected the Duke of       |
| True   |   78407 | Richard II                |          1 | 1.1.1          | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster,            |
| False  |   67000 | A Midsummer Night's Dream |          1 | 1.1.1          | THESEUS         | Now, fair Hippolyta, our nuptial hour                  |
| True   |       4 | Henry IV Part 1           |          1 | 1.1.1          | KING HENRY IV   | So shaken as we are, so wan with care,                 |
| False  |   12664 | All's Well That Ends Well |          1 | 1.1.1          | COUNTESS        | In delivering my son from me, I bury a second husband. |
| True   |    9526 | Henry VI Part 3           |          1 | 1.1.1          | WARWICK         | I wonder how the king escaped our hands.               |
| False  |   52797 | Love's Labour's Lost      |          1 | 1.1.1          | FERDINAND       | Let fame, that all hunt after in their lives,          |
| True   |   28487 | Cymbeline                 |          3 | 1.1.10         | First Gentleman | Is outward sorrow, though I think the king             |
+--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------+

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.

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

Copy

+--------+---------------------------+-----------------+--------------------------------------------------------+
| RESULT | PLAY                      | CHARACTER       | LINE                                                   |
|--------+---------------------------+-----------------+--------------------------------------------------------|
| True   | Pericles                  | LODOVICO        | This king unto him took a fere,                        |
| False  | King John                 | KING JOHN       | Now, say, Chatillon, what would France with us?        |
| True   | King Lear                 | KENT            | I thought the king had more affected the Duke of       |
| False  | Richard II                | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster,            |
| False  | A Midsummer Night's Dream | THESEUS         | Now, fair Hippolyta, our nuptial hour                  |
| False  | Henry IV Part 1           | KING HENRY IV   | So shaken as we are, so wan with care,                 |
| False  | All's Well That Ends Well | COUNTESS        | In delivering my son from me, I bury a second husband. |
| True   | Henry VI Part 3           | WARWICK         | I wonder how the king escaped our hands.               |
| False  | Love's Labour's Lost      | FERDINAND       | Let fame, that all hunt after in their lives,          |
| True   | Cymbeline                 | First Gentleman | Is outward sorrow, though I think the king             |
+--------+---------------------------+-----------------+--------------------------------------------------------+

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;

Copy

+--------+---------------------------+-----------------+--------------------------------------------------------+
| RESULT | PLAY                      | CHARACTER       | LINE                                                   |
|--------+---------------------------+-----------------+--------------------------------------------------------|
| False  | Pericles                  | LODOVICO        | This king unto him took a fere,                        |
| True   | King John                 | KING JOHN       | Now, say, Chatillon, what would France with us?        |
| False  | King Lear                 | KENT            | I thought the king had more affected the Duke of       |
| True   | Richard II                | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster,            |
| False  | A Midsummer Night's Dream | THESEUS         | Now, fair Hippolyta, our nuptial hour                  |
| True   | Henry IV Part 1           | KING HENRY IV   | So shaken as we are, so wan with care,                 |
| False  | All's Well That Ends Well | COUNTESS        | In delivering my son from me, I bury a second husband. |
| False  | Henry VI Part 3           | WARWICK         | I wonder how the king escaped our hands.               |
| False  | Love's Labour's Lost      | FERDINAND       | Let fame, that all hunt after in their lives,          |
| False  | Cymbeline                 | First Gentleman | Is outward sorrow, though I think the king             |
+--------+---------------------------+-----------------+--------------------------------------------------------+

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

Copy

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

Copy

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

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

Copy

+------+------+--------------+------+---------------+-------------+
| COL1 | COL2 | COL3         | COL2 | COL3          | COL4        |
|------+------+--------------+------+---------------+-------------|
|    2 | Mini | Cooper S     | Mini | Cooper S      | Convertible |
|    3 | Mini | Countryman   | Mini | Countryman SE | ALL4        |
|    4 | Mini | Countryman S | Mini | Countryman S  | ALL4        |
+------+------+--------------+------+---------------+-------------+

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

Copy

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

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

L’exemple suivant montre comment utiliser SEARCH deux fois dans la même requête, de sorte que les deux chaînes de recherche doivent renvoyer TRUE pour qu’une ligne soit qualifiée pour le résultat.

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

Copy

+----------------+------------------+-----------------------------------------------------------+
| ACT_SCENE_LINE | CHARACTER        | LINE                                                      |
|----------------+------------------+-----------------------------------------------------------|
| 2.2.1          | KING CLAUDIUS    | Welcome, dear Rosencrantz and Guildenstern!               |
| 2.2.35         | KING CLAUDIUS    | Thanks, Rosencrantz and gentle Guildenstern.              |
| 2.2.36         | QUEEN GERTRUDE   | Thanks, Guildenstern and gentle Rosencrantz:              |
| 2.2.241        | HAMLET           | Guildenstern? Ah, Rosencrantz! Good lads, how do ye both? |
| 4.6.27         | HORATIO          | where I am. Rosencrantz and Guildenstern hold their       |
| 5.2.60         | HORATIO          | So Guildenstern and Rosencrantz go to't.                  |
| 5.2.389        | First Ambassador | That Rosencrantz and Guildenstern are dead:               |
+----------------+------------------+-----------------------------------------------------------+

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

Copy

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;

Copy

+-----------------------+--------------+------------------------+--------------------------+
| CONTAINS_TOYOTA_TESLA | VEHICLE_MAKE | DEALERSHIP             | S.SRC:DEALERSHIP         |
|-----------------------+--------------+------------------------+--------------------------|
| True                  | Toyota       | Tindel Toyota          | "Tindel Toyota"          |
| False                 | Honda        | Valley View Auto Sales | "Valley View Auto Sales" |
+-----------------------+--------------+------------------------+--------------------------+

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;

Copy

+-----------------------+--------------+------------------------+--------------------------+
| CONTAINS_TOYOTA_HONDA | VEHICLE_MAKE | DEALERSHIP             | S.SRC:DEALERSHIP         |
|-----------------------+--------------+------------------------+--------------------------|
| True                  | Toyota       | Tindel Toyota          | "Tindel Toyota"          |
| True                  | Honda        | Valley View Auto Sales | "Valley View Auto Sales" |
+-----------------------+--------------+------------------------+--------------------------+

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

Copy

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

Copy

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

Copy

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

Copy

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;

Copy

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;

Copy

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

Copy

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;

Copy

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

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;

Copy

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

Exemple de données 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;

Copy

+----------------+---------------+--------+-------+-
| name           | type          | kind   | null? |
|----------------+---------------+--------+-------+-
| LINE_ID        | NUMBER(38,0)  | COLUMN | Y     |
| PLAY           | VARCHAR(50)   | COLUMN | Y     |
| SPEECH_NUM     | NUMBER(38,0)  | COLUMN | Y     |
| ACT_SCENE_LINE | VARCHAR(10)   | COLUMN | Y     |
| CHARACTER      | VARCHAR(30)   | COLUMN | Y     |
| LINE           | VARCHAR(2000) | COLUMN | Y     |
+----------------+---------------+--------+-------+-

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

SELECT * FROM lines
  WHERE line_id=34230;

Copy

+---------+--------+------------+----------------+-----------+--------------------------------------------+
| LINE_ID | PLAY   | SPEECH_NUM | ACT_SCENE_LINE | CHARACTER | LINE                                       |
|---------+--------+------------+----------------+-----------+--------------------------------------------|
|   34230 | Hamlet |         19 | 3.1.64         | HAMLET    | To be, or not to be, that is the question: |
+---------+--------+------------+----------------+-----------+--------------------------------------------+

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

Copy