8.6.1 CONTAINS

(1) Specification format

scalar-function-CONTAINS ::= CONTAINS(target-data,search-condition-expression-specification)
 
  target-data ::= value-expression
  search-condition-expression-specification ::= character-string-literal

To Page Top

(2) Explanation of specification format

target-data:

Specifies the data to be searched.

The following rules apply:

• Specify target-data in the form of a value expression. For details about value expressions, see 7.20 Value expression.

• The data type of target-data must be CHAR or VARCHAR.

• You cannot specify a dynamic parameter by itself for target-data.

search-condition-expression-specification:

Specifies a search condition.

The following rules apply:

• You must specify search-condition-expression-specification in the form of a character string literal. For details about character string literals, see 6.3 Literals.

• All characters other than those in the search string and synonym dictionary name in search-condition-expression-specification are assumed to be half-width uppercase.

• Separators cannot be specified.

• For search-condition-expression-specification, one of the following four methods can be specified: simple-string specification, notation-correction-search specification, synonym-search specification, and word-context search specification.

search-condition-expression-specification ::= {simple-string-specification|notation-correction-search-specification|synonym-search-specification
                    |word-context-search-specification}
 
  simple-string-specification ::= "search-character-string"
 
  notation-correction-search-specification ::= {IGNORECASE(simple-string-specification)|SORTCODE(simple-string-specification)}
 
  synonym-search-specification ::= SYNONYM("synonym-dictionary-name",{simple-string-specification|notation-correction-search-specification})
 
  word-context-search-specification ::= {WORDCONTEXT({simple-string-specification|notation-correction-search-specification
                                    |synonym-search-specification})
                      |WORDCONTEXT_PREFIX({simple-string-specification|notation-correction-search-specification})}

simple-string-specification:

Specifies the search string in the following format:

simple-string-specification ::= "search-string"

The following shows an example of a simple-string specification.

Example: "COMPUTER" or "computer"

Enclose the search string (COMPUTER or computer) in double quotation marks (").

Note the following points:

• The characters in search-string are case sensitive.

• To use a double quotation mark (") as an ordinary character in search-string, specify two consecutive double quotation marks ("").

• If search-string is 0-byte character string data, 1 is returned as the execution result. In this case, regardless of target-data, the function judges that target-data contains search-string.

notation-correction-search-specification:

Specify this item when you perform a correction search. For details about correction searches, see Correction search in the HADB Setup and Operation Guide.

Specify the search string in either of the following formats:

notation-correction-search-specification ::= {IGNORECASE(simple-string-specification) | SORTCODE(simple-string-specification)}

• IGNORECASE(simple-string-specification):

  If IGNORECASE is specified, correction search ignores only the difference between half-width uppercase and lowercase letters.

  You can also use the following specification format:

  I(simple-string-specification)

• SORTCODE(simple-string-specification):

  Specify this item when you perform a correction search.

  You can also use the following specification format:

  S(simple-string-specification)

synonym-search-specification:

Specify this item if you want to search for the synonyms specified as the same synonym group in the synonym dictionary at the same time. The following shows the specification format:

synonym-search-specification ::= SYNONYM("synonym-dictionary-name",{simple-string-specification | notation-correction-search-specification})

• synonym-dictionary-name:

  Specifies the name of the synonym dictionary.

The following shows an example of a synonym-search specification.

Note that this example assumes that the following character strings are registered in a synonym dictionary named Dictionary1.

PC,personal computer,microcomputer

Example 1 (Simple-string specification)

SYNONYM("Dictionary1","COMPUTER")

In this case, all of the following words registered in the synonym dictionary are used as search strings: PC, personal computer, and microcomputer.

Example 2 (Notation-correction-search specification)

SYNONYM("Dictionary1",IGNORECASE("COMPUTER"))

In this case, in addition to PC, personal computer, and microcomputer, which are registered in the synonym dictionary, all their variants, such as pc, Personal Computer, and Microcomputer, are used as search strings.

Important

When you register or update a synonym dictionary, if you specify CASESENSITIVE (do not create a synonym dictionary that supports correction search) as a notation-correction option, you cannot include notation-correction-search specification in the synonym-search specification.

word-context-search-specification:

Specify this item when you perform a word-context search. For details about word-context searches, see Word-context search in the HADB Setup and Operation Guide. The following shows the specification format of a word-context search specification:

word-context-search-specification ::= {WORDCONTEXT({simple-string-specification|notation-correction-search-specification
                                  |synonym-search-specification})
                    |WORDCONTEXT_PREFIX({simple-string-specification|notation-correction-search-specification})}

To perform word-based complete-match retrieval, specify WORDCONTEXT. To perform word-based leading-match search, specify WORDCONTEXT_PREFIX.

To Page Top

(3) Rules

1. The scalar function CONTAINS can be specified as the comparison operand on the left side of a comparison predicate. For the comparison operator and the right-side comparison operand, specify >0.

2. If the character encoding that is used on the HADB server is Shift-JIS, notation-correction-search specification cannot be used.

3. The data type of the execution result will be INTEGER.

4. The NOT NULL constraint does not apply to the value of the execution result (the null value is allowed).

5. If the target data is the null value, the execution result will be the null value.

To Page Top

(4) Examples

Assume that you have the table T1 whose column C2 (of the VARCHAR type) contains document information. In the following examples, you use the scalar function CONTAINS to search the document information for specific character strings.

Example 1 (Search using a simple-string specification)

In this example, you retrieve rows whose document information contains the character string COMPUTER.

SELECT "C1" FROM "T1"
    WHERE CONTAINS("C2",'"COMPUTER"') > 0

In this case, half-width uppercase and lowercase letters are distinguished. Therefore, computer and other variants of the specified string are not treated as search strings.

Example 2 (Correction search)

In this example, you retrieve rows whose document information contains the character string COMPUTER and its variants.

SELECT "C1" FROM "T1"
    WHERE CONTAINS("C2",'IGNORECASE("COMPUTER")') > 0

In this case, as a result of notation correction, in addition to the rows that contain COMPUTER, the rows that contain computer, Computer, and other similar variants are to be retrieved.

Example 3 (Correction search)

In this example, you retrieve rows whose document information contains máquina and its variants.

SELECT "C1" FROM "T1"
    WHERE CONTAINS("C2",'SORTCODE("máquina")') > 0

In this case, as a result of notation correction, in addition to the rows that contain máquina, the rows that contain maquina, Maquina, and other variants are to be retrieved.

Example 4 (Synonym search)

Assume that the following character strings are registered in the synonym dictionary Dictionary1 as synonyms: PC, personal computer, and microcomputer. In this example, you search for these character strings in one operation.

SELECT "C1" FROM "T1"
    WHERE CONTAINS("C2",'SYNONYM("Dictionary1","PC")') > 0

Example 5 (Synonym search + correction search)

Assume that the following character strings are registered in the synonym dictionary Dictionary1 as synonyms: PC, personal computer, and microcomputer. In this example, you search for these character strings in one operation. In this case, a correction search is also performed for each of the character strings registered in the synonym dictionary.

Note that the synonym dictionary Dictionary1 must support correction search.

SELECT "C1" FROM "T1"
    WHERE CONTAINS("C2",'SYNONYM("Dictionary1",SORTCODE("PC"))') > 0

When the preceding SELECT statement is run, in addition to the character strings registered in the synonym dictionary (PC, personal computer, microcomputer), their variants, such as pc, Personal Computer, and MICROCOMPUTER, are to be retrieved.

Example 6 (Word-based complete-match word-context search)

In this example, you retrieve rows that contain the English word COMPUTER or its variant from the English document stored in column C2.

SELECT "C1" FROM "T1"
    WHERE CONTAINS("C2",'WORDCONTEXT(IGNORECASE("COMPUTER"))') > 0

In this case, because the correction search (IGNORECASE) is specified, in addition to the rows that contain COMPUTER, the rows that contain computer, Computer, and other similar variants are also retrieved.

Example 7 (Word-based leading-match word-context search)

In this example, you retrieve rows that contain an English word that begins with COMP from the English document stored in column C2.

SELECT "C1" FROM "T1"
    WHERE CONTAINS("C2",'WORDCONTEXT_PREFIX("COMP")') > 0

In this case, the rows that contain a word such as COMPUTER, COMPUTERS, or COMPANY are also retrieved.

To Page Top