Restrictions for search argument syntax

Certain restrictions apply to search argument syntax when you are searching for terms in a text search index.

The following restrictions apply:

If a search string is all uppercase or has irregular casing, the text search server searches for an exact match to the search string. Synonyms are not added to the search, and lemmatization, a process that identifies the root form and different grammatical forms of a word, is not performed.

Example 1: The following CONTAINS statement returns SQL code -171 to indicate that the value of an argument is invalid.

SELECT CUSTKEY  FROM ADMIN5.CUSTOMER FROM  WHERE CONTAINS
(COMMENT, '$') = 1;

Example 2: In the following CONTAINS statement, the dollar sign ('$') is ignored, resulting in a search for only the term 'total'.

SELECT CUSTKEY  FROM ADMIN5.CUSTOMER FROM  WHERE CONTAINS
(COMMENT, 'total$') = 1;

Example 3: The following CONTAINS statement searches for the exact term 'HOTEL' in the COMMENT column.

SELECT CUSTKEY  FROM ADMIN5.CUSTOMER FROM  WHERE CONTAINS
(COMMENT, 'HOTEL') = 1;

Example 4: In the following CONTAINS statement, the search process ignores the SYNONYM option, because the search term contains irregular casing. The search process only searches for the exact term 'hOtel' in the COMMENT column.

SELECT CUSTKEY  FROM ADMIN5.CUSTOMER FROM  WHERE CONTAINS
(COMMENT, 'hOtel', ' SYNONYM = ON ') = 1;