- Kategorien:
Zeichenfolgen- und Binärfunktionen (Volltextsuche)
SEARCH¶
Searches character data (text) in specified columns from one or more tables, including fields in VARIANT, OBJECT, and ARRAY columns. A text analyzer breaks the text into tokens, which are discrete units of text, such as words or numbers. A default analyzer is applied if you don’t specify one.
Weitere Informationen zur Verwendung dieser Funktion finden Sie unter Verwenden der Volltextsuche.
Syntax¶
SEARCH( <search_data>, '<search_string>'
[ , ANALYZER => '<analyzer_name>' ]
[ , SEARCH_MODE => { 'OR' | 'AND' | 'PHRASE' | 'EXACT' } ] )
Erforderliche 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');
Sie können jedoch
*odertable.*nicht mehr als einmal für die Funktion angeben. Im vorherigen Beispiel für die Verknüpfung können 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')
If parentheses aren’t used to separate multiple items, commas are parsed as separators between function arguments.
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_string'Eine VARCHAR-Zeichenfolge, die einen oder mehrere Suchbegriffe enthält. Dieses Argument muss eine literale Zeichenfolge sein. Spaltennamen werden nicht unterstützt. Geben Sie ein Paar einfache Anführungszeichen um die gesamte Zeichenfolge an. Setzen Sie einzelne Begriffe oder Phrasen nicht in Anführungszeichen. Verwenden Sie zum Beispiel:
'blue red green'Nicht verwenden:
'blue' 'red' 'green'The list of terms can be disjunctive or conjunctive when
ORorANDis set for the SEARCH_MODE argument. However, when the'NO_OP_ANALYZER'is used, the query string is matched exactly as it is, with no tokenization and no disjunctive or conjunctive semantics.Searches aren’t case sensitive, except when the
'NO_OP_ANALYZER'is used, so a search for the term'ONCE'against the string'Once upon a time'returns TRUE.Wenn
ORoderANDfür das SEARCH_MODE-Suchargument festgelegt ist, spielt die Reihenfolge der Suchbegriffe in Bezug auf ihr Vorhandensein in den durchsuchten Daten keine Rolle. WannPHRASEoderEXACTfür das SEARCH_MODE-Argument festgelegt ist, muss die Reihenfolge der Suchbegriffe genau mit den durchsuchten Daten übereinstimmen.
Optionale Argumente¶
ANALYZER => 'analyzer_name'Der Name des Textanalysators. Der Name muss in einfache Anführungszeichen gesetzt werden.
The analyzer breaks the search terms (and the text from the column being searched) into tokens. The matching semantics (disjunctive, conjunctive, phrase, or exact) for tokens extracted from the search string and tokens extracted from the columns or fields being searched depends on the value of the SEARCH_MODE argument.
The analyzer tokenizes a string by breaking it where it finds certain delimiters. These delimiters aren’t included in the resulting tokens, and empty tokens aren’t extracted.
Dieser Parameter akzeptiert einen der folgenden Werte:
'DEFAULT_ANALYZER'- Breaks text into tokens based on the following delimiters: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'- Tokenizes based on Unicode segmentation rules that treat spaces and certain punctuation characters as delimiters. These internal rules are designed for natural language searches in many different languages. For example, the default analyzer treats periods in IP addresses and apostrophes in contractions as delimiters, but the Unicode analyzer doesn’t. See 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'- Tokenizes neither the data nor the query string. A search term must exactly match the full text in a column or field, including case sensitivity; otherwise, the SEARCH function returns FALSE. Even if the query string looks like it contains multiple tokens — for example,'sky blue'— the column or field must equal the entire query string exactly. In this case, only'sky blue'is a match;'sky'and'blue'aren’t matches.
Weitere Informationen über das Verhalten der verschiedenen Analysatoren finden Sie unter Wie Suchbegriffe in Token übersetzt werden.
SEARCH_MODE => { 'OR' | 'AND' | 'PHRASE' | 'EXACT' }The semantics used by the search. Set this argument to one of the following values:
'OR'- The function uses disjunctive semantics. There is a match if any of the tokens extracted from the columns or fields being searched match any of the tokens extracted from the search string. For example, if thesearch_stringvalue is'blue red green', the function returns TRUE for a row that containsblueORredORgreenin any of the columns or fields being searched.'AND'- The function uses conjunctive semantics. There is a match if the tokens extracted from at least one of the columns or fields being searched matches all of the tokens extracted from the search string. The matching tokens must all be in one column or field; they can’t be spread across multiple columns or fields. For example, if thesearch_stringvalue is'blue red green', the function returns TRUE for a row that containsblueANDredANDgreenin at least one of the columns or fields being searched.'PHRASE': Die Funktion verwendet die Semantik der Phrasenübereinstimmung. Es gibt eine Übereinstimmung, wenn die Token, die aus mindestens einer der durchsuchten Spalten oder Felder extrahiert wurden, mit allen aus der Suchzeichenfolge extrahierten Token übereinstimmen, einschließlich der Reihenfolge und Umgebung der Token.Die Übereinstimmungssemantik ist die gleiche wie die konjunktive Semantik, mit Ausnahme der folgenden Unterschiede:
Die Reihenfolge der Token muss genau übereinstimmen. Wenn beispielsweise der
search_string-Wert'blue,red,green'ist, gibt die Funktion FALSE fürred,green,bluezurück.Es können keine zusätzlichen Token in die Suchdaten eingefügt werden. Wenn beispielsweise der
search_string-Wert'blue,red,green'ist, gibt die Funktion FALSE fürblue,yellow,red,greenzurück.
'EXACT': Die Funktion verwendet die Semantik der genauen Übereinstimmung. Es gibt eine Übereinstimmung, wenn die Token, die aus mindestens einer der durchsuchten Spalten oder Felder extrahiert wurden, genau mit allen aus der Suchzeichenfolge extrahierten Token übereinstimmen, einschließlich der Trennzeichen.Die Übereinstimmungsregeln sind die gleichen wie bei der Semantik der Phrasenübereinstimmung, mit Ausnahme der folgenden Unterschiede:
Die Trennzeichenfolgen zwischen den Token müssen genau übereinstimmen. Wenn beispielsweise der
search_string-Wert'blue,red,green'ist, gibt die Funktion TRUE für eine Zeile zurück, dieblue,red,greenin mindestens einer/einem der durchsuchten Spalten oder Felder enthält. Die Funktion gibt FALSE für Varianten wieblue|red|greenoderblue, red, greenzurück.Wenn ein Trennzeichen das erste oder letzte Zeichen im
search_string-Wert ist, wird das Trennzeichen beim Abgleich wie ein Zeichen behandelt. Daher können Trennzeichen links und rechts des ersten und letzten Trennzeichens zu einer Übereinstimmung führen. Wenn beispielsweise dersearch_string-Wert'[blue]'ist, gibt die Funktion TRUE fürfoo [blue] bar,[[blue]]und=[blue].zurück, aber nicht für(blue)oderfoo blue bar.
Für alle Suchmodi muss die Zeichenfolge links und rechts durch ein Trennzeichen-Symbol begrenzt werden. Wenn beispielsweise der
search_string-Wert'blue,red,green'ist, gibt die Funktion TRUE für-blue,red,green;zurück. Die Funktion gibt FALSE fürdarkblue,red,greenoderblue,red,greenishzurück.Wenn Sie den
UNICODE_ANALYZERverwenden, wird Semantik mit genauer Übereinstimmung nicht unterstützt. Sie können denDEFAULT_ANALYZERoder denNO_OP_ANALYZERmit Genau-Übereinstimmung-Semantik verwenden, aber im Allgemeinen eignen sich diese Suchsemantiken am besten fürDEFAULT_ANALYZER.Eine Suche, die die Semantik mit der genauen Übereinstimmung mit dem
DEFAULT_ANALYZER-Wert verwendet, verhält sich auf folgende Weis anders als eine Gleichheitssuche oder eine Volltextsuche mitNO_OP_ANALYZER:Eine Gleichheitssuche findet Zeilen, in denen der Wert der Spalte genau mit dem im Prädikat übereinstimmt (einschließlich Groß- und Kleinschreibung), ohne dass zusätzlicher Text das Vorkommen der Suchzeichenfolge umgibt.
Eine Volltextsuche mit dem
NO_OP_ANALYZERähnelt einer Gleichheitssuche, insofern die Groß-/Kleinschreibung beachtet wird und kein zusätzlicher Text zulässig ist.Eine Suche mit Semantik der genauen Übereinstimmung mit dem
DEFAULT_ANALYZERtokenisiert die Spaltenwerte. Es erlaubt zusätzliche Token links und rechts vom Vorkommen der Suchzeichenfolge, solange sie durch Token-Trennzeichen getrennt sind. Bei der Suche wird die Groß-/Kleinschreibung nicht berücksichtigt.
Standard:
'OR'
Rückgabewerte¶
Gibt einen BOOLEAN-Wert zurück:
Der Wert ist TRUE, wenn
search_string-Token mitsearch_data-Token übereinstimmen, basierend auf der im Argument SEARCH_MODE angegebenen Semantik.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¶
The following examples show different ways to use the SEARCH function, starting with simple usage and progressing to more complex use cases:
Platzhaltersuche in allen qualifizierten Spalten in einer Tabelle
Platzhaltersuche für qualifizierte Spalten in verknüpften Tabellen
Suchen von Zeilen mithilfe der Semantik für Phrasenübereinstimmung und genaue Übereinstimmung
Verwendung eines Analysators zur Anpassung des Suchverhaltens
Um die Abfragen in mehreren Beispielen auszuführen, erstellen Sie zuerst die Beispieldaten für die SEARCH-Funktion.
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 disjunkt, da der Standardwert für das Argument SEARCH_MODE 'OR' lautet. (Informationen zu der Tabelle, die hier und in einigen weiteren Beispielen verwendet wird, finden Sie unter Creating the sample data for SEARCH.)
SELECT SEARCH(character, 'king queen'), character
FROM lines
WHERE line_id=4;
+---------------------------------+---------------+
| SEARCH(CHARACTER, 'KING QUEEN') | CHARACTER |
|---------------------------------+---------------|
| True | KING HENRY IV |
+---------------------------------+---------------+
Das folgende Beispiel ähnelt dem vorherigen Beispiel, aber die Suchsemantik ist konjunktiv, da das Argument SEARCH_MODE auf 'AND' gesetzt ist. Die Funktion gibt FALSE zurück, da nur einer der Suchbegriffe (king) in der Spalte character vorhanden ist. Der Begriff queen erscheint nicht in den Suchdaten.
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 |
+-------------------------------------------------------+---------------+
WHERE-Klausel-Suche in einer Spalte¶
Die folgende Abfrage verwendet die Funktion SEARCH, 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 Funktion SEARCH, 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. Das Ergebnis besteht aus Zeilen, in denen king in einer oder mehreren der vier gesuchten Spalten erscheint. Für eine dieser Spalten, act_scene_line, findet die Funktion keine Treffer, die anderen drei Spalten haben jedoch alle Treffer. Das Argument SEARCH_MODE ist standardmäßig auf 'OR' eingestellt.
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. Die Funktion sucht also in den Spalten line und 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 |
+-----------------+-----------------+--------------------------------------------------+----------------+
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. Das Argument SEARCH_MODE ist standardmäßig auf 'OR' eingestellt.
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. Die Funktion sucht also in den Spalten line und act_scene_line.
SELECT SEARCH(* ILIKE '%line', 'king') result, play, character, line
FROM lines
ORDER BY act_scene_line LIMIT 10;
+--------+---------------------------+-----------------+--------------------------------------------------------+
| RESULT | PLAY | CHARACTER | LINE |
|--------+---------------------------+-----------------+--------------------------------------------------------|
| True | Pericles | LODOVICO | This king unto him took a fere, |
| False | King John | KING JOHN | Now, say, Chatillon, what would France with us? |
| True | King Lear | KENT | I thought the king had more affected the Duke of |
| False | Richard II | KING RICHARD II | Old John of Gaunt, time-honour'd Lancaster, |
| False | A Midsummer Night's Dream | THESEUS | Now, fair Hippolyta, our nuptial hour |
| False | Henry IV Part 1 | KING HENRY IV | So shaken as we are, so wan with care, |
| False | All's Well That Ends Well | COUNTESS | In delivering my son from me, I bury a second husband. |
| True | Henry VI Part 3 | WARWICK | I wonder how the king escaped our hands. |
| False | Love's Labour's Lost | FERDINAND | Let fame, that all hunt after in their lives, |
| True | Cymbeline | First Gentleman | Is outward sorrow, though I think the king |
+--------+---------------------------+-----------------+--------------------------------------------------------+
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 verwendet das Argument SEARCH_MODE, um eine konjunktive Semantik anzugeben, die eine Übereinstimmung findet, wenn beide Suchbegriffe zusammen in derselben Spalte vorkommen. Um die konjunktive Semantik zu verwenden, setzen Sie das Argument SEARCH_MODE auf '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: |
+----------------+------------------+-----------------------------------------------------------+
Wenn Sie die konjunktive Semantik verwenden, müssen beide Suchbegriffe in derselben Spalte übereinstimmen. Die folgende Abfrage liefert beispielsweise keine Ergebnisse, da die Begriffe KING und Rosencrantz in keiner Zeile der Suchdaten in derselben Spalte vorkommen.
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 |
|----------------+-----------+------|
+----------------+-----------+------+
Eine ähnliche Abfrage, die eine disjunkte Semantik (die Standardeinstellung) verwendet, indem das Argument SEARCH_MODE auf 'OR' gesetzt wird, findet Übereinstimmungen in den Suchdaten.
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, |
+----------------+------------------+-----------------------------------------------------------+
Suchen von Zeilen mithilfe der Semantik für Phrasenübereinstimmung und genaue Übereinstimmung¶
Sie können die Semantik für Phrasenübereinstimmung und genaue Übereinstimmung für ähnliche, aber leicht unterschiedliche Anwendungsfälle verwenden:
Verwenden Sie die Semantik für Phrasenübereinstimmung, wenn die Wörter und die Reihenfolge der Wörter genau übereinstimmen müssen, es jedoch Unterschiede in den Trennzeichen und den Abständen zwischen den Wörtern geben kann. Um die Semantik für Phrasenübereinstimmung zu verwenden, legen Sie das SEARCH_MODE-Argument auf
'PHRASE'fest.Verwenden Sie die Semantik für genaue Übereinstimmung, wenn die Wörter, die Reihenfolge der Wörter, die Trennzeichen zwischen Wörtern und der Abstand zwischen Wörtern genau übereinstimmen müssen. Um die Semantik für genaue Übereinstimmung zu verwenden, legen Sie das SEARCH_MODE-Argument auf
'EXACT'fest.
Im folgenden Beispiel wird die Semantik für Phrasenübereinstimmung verwendet, um eine exakte Übereinstimmung mit Text in einer längeren Textzeichenfolge zu finden, wobei der Suchtext jedoch andere Trennzeichen und zusätzliche Leerzeichen zwischen den Wörtern aufweist:
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, |
+----------------+-----------+-----------------------------------------------------+
Das folgende Beispiel ist dasselbe wie das vorherige, außer dass es die Semantik genauer Übereinstimmung verwendet, um eine exakte Übereinstimmung mit Text in einer längeren Textzeichenfolge zu finden:
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, |
+----------------+-----------+-----------------------------------------------------+
Zu den üblichen Anwendungsfällen für die Semantik für Phrasenübereinstimmung und genaue Übereinstimmung gehören die Suche nach E-Mail-Adressen, URLsund Telefonnummern. Erstellen Sie für die nächsten Beispiele eine Tabelle mit einer Zeile mit Beispieldaten:
CREATE OR REPLACE TABLE phrase_exact_search_samples (
email VARCHAR,
url VARCHAR,
phone VARCHAR);
INSERT INTO phrase_exact_search_samples VALUES (
'john.robert.doe@mycompany.com',
'http://mycompany.com/product/id-12345.67',
'800-555-0100');
The following example runs a query that uses conjunctive semantics to search the email data by setting the SEARCH_MODE
argument to 'AND' in the first search, phrase-match semantics in the second search, and exact-match semantics
in the third search:
SELECT email AS search_data,
SEARCH(email, 'doe.john.robert@mycompany.com', SEARCH_MODE => 'AND') AS conjunctive_search,
SEARCH(email, 'doe.john.robert@mycompany.com', SEARCH_MODE => 'PHRASE') AS phrase_search,
SEARCH(email, 'doe.john.robert@mycompany.com', SEARCH_MODE => 'EXACT') AS exact_search
FROM phrase_exact_search_samples;
Die Ausgabe zeigt folgende Ergebnisse:
Die
AND-Suche liefert TRUE, obwohl die Bedingungenjohn,robert``und ``doein der Suchzeichenfolge und in den Suchdaten in einer anderen Reihenfolge sind.Die
PHRASE- undEXACT-Suche gibt FALSE zurück, da die Suchbegriffe nicht mit der Reihenfolge der Suchzeichenfolge übereinstimmen.
+-------------------------------+--------------------+---------------+--------------+
| SEARCH_DATA | CONJUNCTIVE_SEARCH | PHRASE_SEARCH | EXACT_SEARCH |
|-------------------------------+--------------------+---------------+--------------|
| john.robert.doe@mycompany.com | True | False | False |
+-------------------------------+--------------------+---------------+--------------+
The following example runs a query that uses conjunctive semantics to search the email data by setting
the SEARCH_MODE argument to 'AND' in the first search, phrase-match semantics in the second search, and exact-match
semantics in the third search:
SELECT email AS search_data,
SEARCH(email, 'john.doe@mycompany.com', SEARCH_MODE => 'AND') AS conjunctive_search,
SEARCH(email, 'john.doe@mycompany.com', SEARCH_MODE => 'PHRASE') AS phrase_search,
SEARCH(email, 'john.doe@mycompany.com', SEARCH_MODE => 'EXACT') AS exact_search
FROM phrase_exact_search_samples;
Die Ausgabe zeigt folgende Ergebnisse:
Die
AND-Suche gibt TRUE zurück, obwohl das zusätzliche Tokenrobertin die Suchdaten eingefügt ist.Die
PHRASE- undEXACT-Suche gibt FALSE zurück, da diese Suchsemantik keine Übereinstimmung findet, wenn zusätzliche Token in die Suchdaten eingefügt werden.
+-------------------------------+--------------------+---------------+--------------+
| SEARCH_DATA | CONJUNCTIVE_SEARCH | PHRASE_SEARCH | EXACT_SEARCH |
|-------------------------------+--------------------+---------------+--------------|
| john.robert.doe@mycompany.com | True | False | False |
+-------------------------------+--------------------+---------------+--------------+
Im folgenden Beispiel wird eine Abfrage ausgeführt, die in der ersten Suche anhand der Semantik für Phrasensuche die E-Mail-Daten durchsucht und in der zweiten Suche die Semantik für genaue Übereinstimmung:
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;
Die Ausgabe zeigt, dass die PHRASE-Suche TRUE zurückgibt, obwohl die Trennzeichen in der E-Mail-Adresse in der Suchzeichenfolge Bindestriche statt Punkte zwischen john, robert``und ``doe sind. Die EXACT-Suche liefert FALSE, da bei der Semantik für genaue Übereinstimmung die Trennzeichen in der Zeichenfolge der Suche genau mit den Suchdaten übereinstimmen müssen:
+-------------------------------+---------------+--------------+
| SEARCH_DATA | PHRASE_SEARCH | EXACT_SEARCH |
|-------------------------------+---------------+--------------|
| john.robert.doe@mycompany.com | True | False |
+-------------------------------+---------------+--------------+
Im folgenden Beispiel wird eine Abfrage ausgeführt, die in der ersten Suche anhand der Semantik für Phrasensuche die URL-Daten durchsucht und in der zweiten Suche die Semantik für genaue Übereinstimmung:
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;
Die Ausgabe zeigt, dass die PHRASE-Suche TRUE zurückgibt, obwohl in der Produkt-ID das Trennzeichen in der URL in der Suchzeichenfolge ein Unterstrich anstelle eines Punktes ist. Die EXACT-Suche liefert FALSE:
+------------------------------------------+---------------+--------------+
| SEARCH_DATA | PHRASE_SEARCH | EXACT_SEARCH |
|------------------------------------------+---------------+--------------|
| http://mycompany.com/product/id-12345.67 | True | False |
+------------------------------------------+---------------+--------------+
Im folgenden Beispiel wird eine Abfrage ausgeführt, die in der ersten Suche anhand der Semantik für Phrasensuche die Telefonnummerndaten durchsucht und in der zweiten Suche die Semantik für genaue Übereinstimmung:
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;
Die Ausgabe zeigt, dass die PHRASE-Suche TRUE zurückgibt, obwohl die Trennzeichen für die Telefonnummer in der Suchzeichenfolge Punkte statt Bindestriche sind. Die EXACT-Suche liefert FALSE:
+--------------+---------------+--------------+
| SEARCH_DATA | PHRASE_SEARCH | EXACT_SEARCH |
|--------------+---------------+--------------|
| 800-555-0100 | True | False |
+--------------+---------------+--------------+
Die folgenden Beispiele verwenden die SEARCH-Funktion in einer WHERE-Klausel zur Abfrage der phrase_exact_search_samples-Tabelle. Fügen Sie zunächst eine weitere Zeile in die Tabelle ein:
INSERT INTO phrase_exact_search_samples VALUES (
'jane.smith@mycompany.com',
'http://mycompany.com/product/id-89012.34',
'800-555-0199');
Das folgende Beispiel sucht nach einer exakten Übereinstimmung mit der Telefonnummer 800-555-0100 in den Tabellendaten:
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 |
+-------------------------------+------------------------------------------+--------------+
Das folgende Beispiel ist dasselbe wie das vorherige, verwendet aber disjunkte Semantik anstelle von exakter Übereinstimmungs-Semantik, sodass jede Telefonnummer, die 800 oder 555 enthält, eine Übereinstimmung ist:
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 |
+-------------------------------+------------------------------------------+--------------+
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¶
The following examples show how to adjust the behavior of the SEARCH function by specifying a
non-default analyzer: 'UNICODE_ANALYZER' or 'NO_OP_ANALYZER'.
The first example uses the 'NO_OP_ANALYZER' to test whether the string 1.2.500 matches the exact contents
of the act_scene_line column for any row in the lines table. Two rows qualify for the search.
SELECT line_id, act_scene_line FROM lines
WHERE SEARCH(act_scene_line, '1.2.500', ANALYZER=>'NO_OP_ANALYZER');
+---------+----------------+
| LINE_ID | ACT_SCENE_LINE |
|---------+----------------|
| 91998 | 1.2.500 |
| 108464 | 1.2.500 |
+---------+----------------+
If you remove 'NO_OP_ANALYZER' as an argument to the function for this example, the search returns a large
number of rows. The default analyzer treats 1, 2, and 500 as distinct tokens; therefore, the function
returns TRUE for all of the rows where 1, 2, or 500 exist in any order or combination.
If you change this query to include only the prefix 1.2 for the second argument, the default analyzer
returns TRUE, but the 'UNICODE_ANALYZER' and 'NO_OP_ANALYZER' both return FALSE. The default analyzer treats
periods in these values as delimiters, but the Unicode analyzer doesn’t.
The following two queries show another effect of using the 'UNICODE_ANALYZER' instead of the default analyzer. The
first query, using the 'UNICODE_ANALYZER', returns only one row. The extra single quote in the second
argument is there to escape the single quote for the apostrophe. See 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'
Creating the sample data for 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')
;