SQL Reference - CONTAINS

CONTAINS( column_name, ‘{text | “phrase” | prefix_ | FORMSOF (INFLECTIONAL, word) }’ [, LANGUAGE language_code ] )

column_name: This parameter specifies the column in the table on which the CONTAINS predicate is to operate. It will impose the full-text search on the selected column.
‘{text | “phrase” | prefix_ | formsof (inflectional, word) }’: This is the text to search for in the specified column. It can be a simple text, a phrase enclosed in double quotations, a prefix search certificate by ’*’, or use the FORMSOF function to search for certain inflected forms of a specified word.
language language_code: This is an optional parameter. It specifies the language in which the given words or phrases are to be searched. If not provided, the system defaults to the language of the column as specified in the full-text index definition.

Example

DECLARE @Phrase VARCHAR(10) = 'SQL Server';
SELECT *
FROM Products
WHERE CONTAINS(ProductName, @Phrase);

Output

ProductID   ProductName
----------  ------------------------
2           SQL Server Standard Edition
5           SQL Server Enterprise Edition
21          SQL Server Developer Edition

Explanation

The CONTAINS function is used in the SQL WHERE clause to search a column containing specific word or phrase. In the example, it searches the “ProductName” column in the “Products” table to find any products that contain the phrase “SQL Server”.

CONTAINS( column_to_search, text_to_find, label) RETURN NUMBER;

column_to_search: The target column within the table which will be searched. This field denotes the specific column in the database where the text_to_find should be looked for.
text_to_find: The text string to be searched within the ‘column_to_search’. This is the specific value or pattern of data that the function is attempting to locate within the specific data column.
label: An optional parameter that assigns a label to the CONTAINS operation so that its score can be referenced in the ORDER BY clause to sort the result. The RETURN NUMBER function with the label parameter signifies that Oracle will produce a numerical score for each row based on the relevance of the text_to_find in column_to_search. The higher the number, the higher the relevance.

Example

SELECT product_name
FROM products
WHERE CONTAINS(product_name, 'TV', 1) > 0;

Output

PRODUCT_NAME
------------
Smart TV
LED TV
4K TV

Explanation

In the example above, the CONTAINS function is used to find all the products with ‘TV’ in their name. The returned output represents the names of the products that match the condition in the CONTAINS function.