- Kategorien:
Zeichenfolgen- und Binärfunktionen (Volltextsuche)
SEARCH¶
Durchsucht Zeichendaten (Text) in angegebenen Spalten aus einer oder mehreren Tabellen, einschließlich der Felder in den VARIANT-, OBJECT- und ARRAY-Spalten. Ein Textanalysator zerlegt den Text in Token, d. h. diskrete Texteinheiten, wie z. B. Wörter oder Zahlen. Es wird ein Standardanalysator verwendet, wenn Sie keinen angeben.
Weitere Informationen zur Verwendung dieser Funktion finden Sie unter Verwenden der Volltextsuche.
Syntax¶
SEARCH( <search_data>, <search_string> [ , ANALYZER => '<analyzer_name>' ] )
Argumente¶
search_dataDie Daten, die Sie suchen möchten, ausgedrückt als durch Kommas getrennte Listen von Zeichenfolgen, Spaltennamen oder Pfaden zu Feldern in VARIANT-Spalten. Die Suchdaten können auch aus einer einzelnen literalen Zeichenfolge bestehen, was nützlich für das Testen der Funktion sein kann.
Sie können das Platzhalterzeichen (
*) angeben, wobei sich*auf alle qualifizierten Spalten in allen Tabellen ausdehnt, die sich im Bereich der Funktion befinden. Qualifizierte Spalten sind diejenigen, die die Datentypen VARCHAR (Text), VARIANT, ARRAY und OBJECT haben. VARIANT-, ARRAY- und OBJECT-Daten werden für die Suche in Text umgewandelt.Wenn Sie einen Platzhalter an die Funktion übergeben, können Sie den Platzhalter mit dem Namen oder Alias für die Tabelle qualifizieren. Um beispielsweise alle Spalten aus der Tabelle
mytablezu übergeben, geben Sie Folgendes ein:(mytable.*)
Sie können auch die Schlüsselwörter ILIKE und EXCLUDE zum Filtern verwenden:
ILIKE filtert nach Spaltennamen, die dem angegebenen Muster entsprechen. Es ist nur ein Muster erlaubt. Beispiel:
(* ILIKE 'col1%')
EXCLUDE filtert Spaltennamen heraus, die nicht mit der angegebenen Spalte oder den angegebenen Spalten übereinstimmen. Beispiel:
(* EXCLUDE col1) (* EXCLUDE (col1, col2))
Qualifizierer sind gültig, wenn Sie diese Schlüsselwörter verwenden. Das folgende Beispiel verwendet das Schlüsselwort ILIKE, um nach allen Spalten zu filtern, die dem Muster
col1%in der Tabellemytableentsprechen:(mytable.* ILIKE 'col1%')
Die Schlüsselwörter ILIKE und EXCLUDE können nicht in einem einzigen Funktionsaufruf kombiniert werden.
Weitere Informationen zu den Schlüsselwörtern ILIKE und EXCLUDE finden Sie im Abschnitt „Parameter“ unter SELECT.
Sie können Spalten aus mehr als einer Tabelle durchsuchen, wenn mehrere Tabellen in dem Bereich enthalten sind, indem Sie Tabellen verknüpfen oder den UNION Set-Operator verwenden. Um alle Spalten in der Ausgabe einer Verknüpfung oder einer UNION-Abfrage zu durchsuchen, können Sie den unqualifizierten Platzhalter
*wie folgt verwenden:SELECT * FROM t AS T1 JOIN t AS T2 USING (col1) WHERE SEARCH((*), 'string');
Um bei der Verknüpfung von Tabellen nach bestimmten Spalten zu suchen, müssen Sie möglicherweise die Spaltennamen qualifizieren (z. B.
table2.colname). Sie können auch einen qualifizierten Platzhalter*wie folgt verwenden:SELECT * FROM t AS T1 JOIN t AS T2 USING (col1) WHERE SEARCH((T2.*), 'string');
Beachten Sie jedoch, dass Sie
*odertable.*nur einmal für die Funktion angeben können. Im vorherigen Verknüpfungsbeispiel konnten SieSEARCH((T1.*, T2.*), 'string')nicht angeben. Diese Syntax gibt einen Fehler zurück.Für das Argument
search_datasind Klammern erforderlich, wenn*,table.*oder mehrere Elemente aufgeführt sind. Beispiel:SEARCH((col1, col2, col3), 'string') SEARCH((t1.*), 'string') SEARCH((*), 'string')
Wenn keine Klammern zur Trennung mehrerer Elemente verwendet werden, werden Kommas als Trennzeichen zwischen Funktionsargumenten analysiert.
Siehe auch Beispiele für erwartete Fehlerfälle.
Sie können Felder in VARIANT-Daten durchsuchen, indem Sie den Spaltennamen, einen Doppelpunkt oder einen Punkt und die durch Punkte getrennten Teilfelder angeben. Beispiel:
colname:fieldname.subfieldname. Weitere Informationen zur Angabe von Feldern in solchen Spalten finden Sie unter Durchsuchen semistrukturierter Daten.search_stringEine VARCHAR-Zeichenfolge, die einen oder mehrere Suchbegriffe enthält. Dieses Argument muss eine Literal Zeichenfolge sein; Spaltennamen werden nicht unterstützt. Geben Sie ein Paar einfache Anführungszeichen um die gesamte Zeichenfolge an. Geben Sie keine Anführungszeichen um einzelne Begriffe oder Phrasen an. Verwenden Sie zum Beispiel:
'blue red green'Verwenden Sie nicht:
'blue' 'red' 'green'Die Liste der Begriffe ist disjunktiv In diesem Fall würde die Suche nach Zeilen suchen, die
blueORredORgreenenthalten. Wenn jedoch NO_OP_ANALYZER verwendet wird, wird die Zeichenfolge der Abfrage genau so abgeglichen, wie sie ist, ohne Tokenisierung und ohne disjunkte Semantik.Bei der Suche wird nicht zwischen Groß- und Kleinschreibung unterschieden (außer bei der Verwendung von NO_OP_ANALYZER). Eine Suche nach dem Begriff
'ONCE'in der Zeichenfolge'Once upon a time'gibt also TRUE zurück.Die Reihenfolge der Suchbegriffe spielt in Bezug auf ihr Vorhandensein in den durchsuchten Daten keine Rolle.
ANALYZER => 'analyzer_name'Optionales Argument, das den Namen des Textanalysators angibt. Der Name muss in einfache Anführungszeichen gesetzt werden.
Der Analysator zerlegt die Suchbegriffe (und den Text aus der durchsuchten Spalte) in Token. Eine Zeile ist übereinstimmend, wenn eines der aus der Suchzeichenfolge extrahierten Token genau mit einem Token übereinstimmt, das aus einer der durchsuchten Spalten oder Felder extrahiert wurde.
Der Analysator übersetzt eine Zeichenfolge in Token, indem er sie an bestimmten Trennzeichen aufbricht. Diese Trennzeichen sind in den resultierenden Token nicht enthalten, und leere Token werden nicht extrahiert.
Dieser Parameter akzeptiert einen der folgenden Werte:
DEFAULT_ANALYZER: Unterteile den Text anhand folgender Trennzeichen in Token:
Zeichen
Unicode-Code
Beschreibung
U+0020Leerzeichen
[U+005BLinke eckige Klammer
]U+005DRechte eckige Klammer
;U+003BSemikolon
<U+003CWeniger-als-Zeichen
>U+003EGrößer-als-Zeichen
(U+0028Linke Klammer
)U+0029Rechte Klammer
{U+007BLinke geschweifte Klammer
}U+007DRechte geschweifte Klammer
|U+007CVertikaler Balken
!U+0021Ausrufezeichen
,U+002CKomma
'U+0027Apostroph
"U+0022Anführungszeichen
*U+002AAsterisk
&U+0026Ampersand
?U+003FFragezeichen
+U+002BPluszeichen
/U+002FSchrägstrich
:U+003ADoppelpunkt
=U+003DGleichheitszeichen
@U+0040Bei Zeichen
.U+002EPunkt (Punkt)
-U+002DBindestrich
$U+0024Dollarzeichen
%U+0025Prozentzeichen
\U+005CBackslash
_U+005FUnterstrich (untere Linie)
\nU+000ANeue Zeile (Zeilenvorschub)
\rU+000DZeilenumbruch
\tU+0009Horizontale Registerkarte
UNICODE_ANALYZER: Tokenisierung auf der Grundlage von Unicode-Segmentierungsregeln, die Leerzeichen und bestimmte Satzzeichen als Trennzeichen behandeln. Diese internen Regeln sind für die Suche in natürlicher Sprache (in vielen verschiedenen Sprachen) konzipiert. Zum Beispiel behandelt der Standardanalysator Punkte in IP-Adressen und Apostrophe in Kontraktionen als Trennzeichen, der Unicode-Analysator jedoch nicht. Siehe Verwendung eines Analysators zur Anpassung des Suchverhaltens.
Weitere Informationen über den Algorithmus zur Segmentierung von Unicode-Text finden Sie unter https://unicode.org/reports/tr29/.
NO_OP_ANALYZER: Übersetzt weder die Daten noch die Zeichenfolge der Abfrage in Token. Ein Suchbegriff muss exakt mit dem vollständigen Text in einer Spalte oder einem Feld übereinstimmen, einschließlich der Groß- und Kleinschreibung; andernfalls gibt die Funktion SEARCH FALSE zurück. Auch wenn die Zeichenfolge der Abfrage so aussieht, als enthielte sie mehrere Token (z. B.
'sky blue'), muss die Spalte oder das Feld genau der gesamten Zeichenfolge der Abfrage entsprechen. In diesem Fall ist nur'sky blue'eine Übereinstimmung;'sky'und'blue'sind keine Übereinstimmungen.
Weitere Informationen über das Verhalten der verschiedenen Analysatoren finden Sie unter Wie Suchbegriffe in Token übersetzt werden.
Rückgabewerte¶
Gibt einen BOOLEAN-Wert zurück.
Der Wert ist TRUE, wenn ein oder mehrere
search_stringToken insearch_datagefunden werden.Gibt NULL zurück, wenn eines dieser Argumente NULL ist.
Andernfalls wird FALSE zurückgegeben.
Nutzungshinweise¶
Die SEARCH-Funktion arbeitet nur mit VARCHAR-, VARIANT-, ARRAY- und OBJECT-Daten. Die Funktion gibt einen Fehler zurück, wenn das Argument
search_datakeine Daten dieser Datentypen enthält. Wenn das Argumentsearch_datasowohl Daten von unterstützten als auch von nicht unterstützten Datentypen enthält, sucht die Funktion die Daten der unterstützten Datentypen und ignoriert die Daten der nicht unterstützten Datentypen. Weitere Beispiele finden Sie unter Beispiele für erwartete Fehlerfälle.Sie können eine FULL_TEXT-Suchoptimierung für Spalten hinzufügen, die das Ziel von SEARCH-Funktionsaufrufen sind, indem Sie den Befehl ALTER TABLE verwenden. Beispiel:
ALTER TABLE lines ADD SEARCH OPTIMIZATION ON FULL_TEXT(play, character, line);
Weitere Informationen finden Sie unter Aktivieren der FULL_TEXT-Suchoptimierung.
Wie Suchbegriffe in Token übersetzt werden¶
In der folgenden Tabelle finden Sie einige Beispiele dafür, wie eingegebene Suchbegriffe in Token aufgeteilt werden. Dies hängt von den Regeln ab, die der verwendete Analysator anwendet. In der Tabelle geben die Kommas an, wo die Token geteilt werden (falls sie geteilt werden).
Suchbegriff(e) |
Token: DEFAULT_ANALYZER |
Token: UNICODE_ANALYZER |
NO_OP_ANALYZER (nicht geteilt) |
|---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Beispiele¶
Die folgenden Beispiele zeigen verschiedene Möglichkeiten für die Verwendung der SEARCH-Funktion, angefangen bei der einfachen Verwendung bis hin zu komplexeren Anwendungsfällen.
Abgleich mit einem Literal¶
Das einfachste Beispiel für die SEARCH-Funktion ist ein Test auf TRUE oder FALSE für eine Zeichenfolge. Das erste Beispiel gibt TRUE zurück, weil die Literale für das erste und zweite Argument übereinstimmen, da der Vergleich nicht zwischen Groß- und Kleinschreibung unterscheidet.
SELECT SEARCH('king','KING');
+-----------------------------+
| SEARCH('KING','KING') |
|-----------------------------|
| True |
+-----------------------------+
Das zweite Beispiel gibt FALSE zurück, weil das Token 32 nicht in dem für das erste Argument angegebenen Literal 5.1.33 vorkommt.
SELECT SEARCH('5.1.33','32');
+-----------------------------+
| SEARCH('5.1.33','32') |
|-----------------------------|
| False |
+-----------------------------+
Abgleich mit einer Spaltenreferenz¶
Dieses Beispiel verwendet eine Spalte in einer Tabelle als erstes Argument. Die Funktion gibt TRUE zurück, da einer der Suchbegriffe (king) in der Spalte character vorhanden ist. Die Liste der Begriffe ist disjunktiv (Informationen zu der Tabelle, die hier und in einigen weiteren Beispielen verwendet wird, finden Sie unter Beispieldaten für SEARCH.)
SELECT SEARCH(character, 'king queen'),character
FROM lines
WHERE line_id=4;
+--------------------------------------+---------------+
| SEARCH(CHARACTER,'KING QUEEN') | CHARACTER |
|--------------------------------------+---------------|
| True | KING HENRY IV |
+--------------------------------------+---------------+
WHERE-Klausel-Suche in einer Spalte¶
Die folgende Abfrage verwendet die SEARCH-Funktion, um Zeilen zu finden, die das Wort „wherefore“ in der Spalte line enthalten:
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? |
+---------+----------------------+------------+----------------+-----------+-----------------------------------------------------+
WHERE-Klausel-Suche über mehrere Spalten¶
Die folgende Abfrage verwendet die SEARCH-Funktion, um Zeilen zu finden, die das Wort „king“ in der Spalte play, in der Spalte character oder in beiden Spalten enthalten. Für das erste Argument sind Klammern erforderlich.
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 |
+---------------------------+-----------------+
Platzhaltersuche in allen qualifizierten Spalten in einer Tabelle¶
Sie können das Zeichen * (oder table.*) als erstes Argument für die SEARCH-Funktion verwenden, wie in diesem Beispiel gezeigt. Die Suche bezieht sich auf alle qualifizierten Spalten in der Tabelle, aus der Sie auswählen, in diesem Fall die Tabelle lines.
Die Tabelle lines hat vier Spalten mit Datentypen, die von der Suchfunktion unterstützt werden. Beachten Sie, dass das Ergebnis aus Zeilen besteht, in denen „king“ in einer oder mehreren der vier gesuchten Spalten vorkommt. Für eine dieser Spalten, act_scene_line, findet die Funktion keine Treffer, die anderen drei Spalten haben jedoch alle Treffer.
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 |
+-----------------+-----------------+----------------------------------------------------+----------------+
Sie können auch die Schlüsselwörter ILIKE und EXCLUDE zum Filtern verwenden. Weitere Informationen zu diesen Schlüsselwörtern finden Sie unter SELECT.
Diese Suche verwendet das Schlüsselwort ILIKE, um nur in Spalten zu suchen, die mit der Zeichenfolge line enden.
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 |
+-----------------+-----------------+--------------------------------------------------+----------------+
Diese Suche verwendet das Schlüsselwort EXCLUDE, damit die Funktion nicht die Daten in der Spalte character durchsucht.
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 |
+-----------------+-----------------+----------------------------------------------------+----------------+
Platzhaltersuche in einer SELECT-Liste¶
Sie können das Zeichen * (oder table.*) in einer SELECT-Liste verwenden, wie in diesen Beispielen gezeigt.
Die folgende Operation bezieht sich auf alle qualifizierten Spalten in der Tabelle, aus der Sie auswählen. In diesem Fall ist das die Tabelle lines. Die Suche ergibt True, wenn „king“ in einer oder mehreren der vier gesuchten Spalten vorkommt.
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 |
+--------+---------+---------------------------+------------+----------------+-----------------+--------------------------------------------------------+
Sie können auch die Schlüsselwörter ILIKE und EXCLUDE zum Filtern verwenden. Weitere Informationen zu diesen Schlüsselwörtern finden Sie unter SELECT.
Diese Suche verwendet das Schlüsselwort ILIKE, um nur in Spalten zu suchen, die mit der Zeichenfolge line enden.
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 |
+--------+---------------------------+-----------------+--------------------------------------------------------+
Diese Suche verwendet das Schlüsselwort EXCLUDE, sodass die Funktion nicht die Daten in den Spalten play oder line durchsucht.
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 |
+--------+---------------------------+-----------------+--------------------------------------------------------+
Platzhaltersuche für qualifizierte Spalten in verknüpften Tabellen¶
Dieses Beispiel verwendet zwei kleine Tabellen, die Informationen über Automodelle enthalten. Die Tabelle t1 hat zwei Zeichenspalten und die Tabelle t2 drei. Sie können die Tabellen wie folgt erstellen und laden:
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');
Die Ergebnisse der folgenden beiden Abfragen unterscheiden sich, da die Suche über t1.* und t2.* erfolgt. Nur zwei Spalten von t1 sind für die Suche qualifiziert, aber drei Spalten von t2 sind qualifiziert.
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 |
+------+------+--------------+------+---------------+-------------+
Platzhaltersuche in der Ausgabe einer UNION-Unterabfrage¶
Das folgende Beispiel verwendet dieselben zwei Tabellen wie das vorherige Beispiel. In diesem Fall wird die Suche auf alle qualifizierten Spalten von t3 angewendet, der Tabelle, die das Ergebnis der Unterabfrage ist. Die Unterabfrage berechnet die UNION der ersten drei Spalten in t1 und t2 (fünf Zeilen). Die Suche liefert zwei übereinstimmende Zeilen aus dem Ergebnis von 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 |
+------+------+--------------+
Suche nach Zeilen, die mehreren Zeichenfolgen entsprechen¶
Das folgende Beispiel zeigt, wie Sie SEARCH zweimal in derselben Abfrage verwenden, sodass beide Zeichenfolgen TRUE ergeben müssen, damit eine Zeile für das Ergebnis in Frage kommt.
SELECT act_scene_line, character, line
FROM lines
WHERE SEARCH(line, 'Rosencrantz')
AND SEARCH(line, 'Guildenstern')
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: |
+----------------+------------------+-----------------------------------------------------------+
Suche nach VARIANT- und VARCHAR-Daten in einer Verknüpfung¶
Das folgende Beispiel zeigt eine Verknüpfung von zwei Tabellen, car_rentals und car_sales, wobei die Suche auf Spalten in beiden Tabellen angewendet wird. Die Tabelle car_sales enthält VARIANT-Daten. Die Tabelle car_sales und ihre Daten sind unter Abfragen von semistrukturierten Daten beschrieben. Die folgenden SQL-Anweisungen erstellen die Tabelle car_rentals und fügen Daten in sie ein:
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');
Führen Sie die Abfrage aus:
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" |
+-----------------------+--------------+------------------------+--------------------------+
In diesem zweiten Beispiel werden für dieselben Daten andere Suchbegriffe verwendet:
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" |
+-----------------------+--------------+------------------------+--------------------------+
Verwendung eines Analysators zur Anpassung des Suchverhaltens¶
Die folgenden Beispiele zeigen, wie Sie das Verhalten der SEARCH-Funktion anpassen können, indem Sie einen nicht standardmäßigen Analysator angeben: UNICODE_ANALYZER oder NO_OP_ANALYZER.
Das erste Beispiel verwendet NO_OP_ANALYZER, um zu testen, ob die Zeichenfolge 1.2.500 mit dem exakten Inhalt der Spalte act_scene_line für eine beliebige Zeile der Tabelle lines übereinstimmt. Zwei Reihen sind für die Suche qualifiziert.
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 |
+---------+----------------+
Wenn Sie für dieses Beispiel NO_OP_ANALYZER als Argument für die Funktion entfernen, gibt die Suche eine große Anzahl von Zeilen zurück. Der Standardanalysator behandelt 1, 2 und 500 als unterschiedliche Token. Daher gibt die Funktion TRUE für alle Zeilen zurück, in denen 1, 2 oder 500 vorkommen (in beliebiger Reihenfolge oder Kombination).
Wenn Sie diese Abfrage so ändern, dass sie nur das Präfix 1.2 für das zweite Argument enthält, gibt der Standardanalysator TRUE zurück, aber UNICODE_ANALYZER und NO_OP_ANALYZER geben beide FALSE zurück. Der Standardanalysator behandelt Zeiträume in diesen Werten als Trennzeichen, der Unicode-Analysator jedoch nicht.
Die folgenden beiden Abfragen zeigen eine weitere Auswirkung der Verwendung des UNICODE_ANALYZER anstelle des Standardanalysators. Die erste Abfrage, die den UNICODE_ANALYZER verwendet, gibt nur eine Zeile zurück. Beachten Sie, dass das zusätzliche einfache Anführungszeichen im zweiten Argument dazu dient, das einfache Anführungszeichen für das Apostroph zu umgehen. Siehe Zeichenfolgenkonstanten in einfachen Anführungszeichen.
SELECT DISTINCT(play)
FROM lines
WHERE SEARCH(play, 'love''s', ANALYZER=>'UNICODE_ANALYZER');
+----------------------+
| PLAY |
|----------------------|
| Love's Labour's Lost |
+----------------------+
Die zweite Abfrage, die den Standardanalysator verwendet, gibt vier Zeilen zurück, da der Standardanalysator das Apostrophzeichen als Trennzeichen behandelt. Jede Zeichenfolge, die den Buchstaben „s“ als Token enthält, eignet sich für die Suche. In diesem Beispiel gibt die Funktion TRUE für jede Zeichenfolge zurück, die ein „Apostroph-s“ enthält ('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 |
+---------------------------+
Beispiele für erwartete Fehlerfälle¶
Die folgenden Beispiele zeigen Abfragen, die erwartete Syntaxfehler zurückgeben.
Dieses Beispiel schlägt fehl, weil 5 kein unterstützter Datentyp für das Argument search_string ist.
SELECT SEARCH(line, 5) FROM lines;
001045 (22023): SQL compilation error:
argument needs to be a string: '1'
Dieses Beispiel schlägt fehl, weil für das Argument search_data keine Spalte eines unterstützten Datentyps angegeben ist.
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.
Dieses Beispiel ist erfolgreich, weil für das Argument search_data eine Spalte eines unterstützten Datentyps angegeben ist. Die Funktion ignoriert die Spalte line_id, da es sich nicht um einen unterstützten Datentyp handelt
SELECT SEARCH((line_id, play), 'dream') FROM lines
ORDER BY play LIMIT 5;
+----------------------------------+
| SEARCH((LINE_ID, PLAY), 'DREAM') |
|----------------------------------|
| True |
| True |
| False |
| False |
| False |
+----------------------------------+
Dieses Beispiel schlägt fehl, weil für das erste Argument mehrere Zeichenfolgen ohne Klammern aufgeführt sind, was zu nicht übereinstimmenden Argumenten führt:
SELECT SEARCH('docs@snowflake.com', 'careers@snowflake.com', '@');
001881 (42601): SQL compilation error: Expected 1 named argument(s), found 0
Dieses Beispiel schlägt fehl, weil für das erste Argument mehrere Spaltennamen ohne Klammern aufgeführt werden, wodurch zu viele Argumente entstehen:
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
Dieses Beispiel schlägt fehl, weil ein Spaltenname nicht als Zeichenfolge für die Suche akzeptiert wird.
SELECT SEARCH(line, character) FROM lines;
001015 (22023): SQL compilation error:
argument 2 to function SEARCH needs to be constant, found 'LINES.CHARACTER'
Beispieldaten für SEARCH¶
Einige der Beispiele in diesem Abschnitt fragen eine Tabelle ab, die Text aus Thearterstücken von Shakespeare enthält. Jede Zeile des Textes wird in einer einzelnen Zeile der Tabelle gespeichert. In anderen Spalten finden Sie den Namen des Stücks, den Namen des Zeichens und so weiter. Die Tabelle lines hat die folgende Struktur:
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 |
+----------------+---------------+--------+-------+-
Eine einzelne Zeile in dieser Tabelle sieht zum Beispiel so aus:
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: |
+---------+--------+------------+----------------+-----------+--------------------------------------------+
Wenn Sie die Beispiele in diesem Abschnitt ausführen möchten, erstellen Sie diese Tabelle, indem Sie die folgenden Befehle ausführen:
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')
;