Table of Contents
Overview of SQL Contains
The SQL contains is the SQL predicate Boolean function used with WHERE clause in the SQL Select statement to perform full-text search operations like search for a word, the prefix of a word, a word near another word, synonym of a word, etc... On full-text indexed columns containing character-based data types like string, char, and so on
Although SQL contains is not a standard SQL function, many of the database SQL Contains function argument depending on which database system we are using with,
For Microsoft SQL Server, SQL Contains function used to searches a text search in text column value-based criteria that are specified in a search argument and returns a number with either a true or false result, it will be 1 (true) if it finds a match and 0 (false) if it doesn’t. The first argument is the name of the table column you want to be searched; the second argument is the substring you want to find in the first argument column value
Comparison between SQL Contains and SQL LIKE
SQL Contains | SQL LIKE |
---|---|
SQL Contains is a predicate that can be used to search for a word, the prefix of a word, a word near another word, synonym of a word, etc. | SQL LIKE is an operator which is used to find whether a text string matches with a specified pattern or substring |
SQL Contains performs full-text search only on full-text indexed columns. | SQL LIKE search by matching regular expression Patten in the text column |
SQL Contains supports only wildcard character asterisk (*) | SQL LIKE supports all regular characters or wildcard characters like %, _, [] and [^] in a pattern |
SQL Contain operator searched in the expression or condition in nvarchar datatype | SQL LIKE perform the matching operation with a string that can have a maximum of eight thousand bytes |
SQL Contains Syntax
CONTAINS ( { column_name | ( column_list ) | * } , '<contains_search_condition>' [ { <AND> | <AND NOT> | <OR> } ]
<contains_search_condition> [ ...n ] );
Here,
- column_name: It is the name of a full-text indexed column of the table specified in the FROM clause, The datatype pf columns can be char, varchar, nchar, nvarchar, text, ntext, image, XML, varbinary, or varbinary(max)
- column_list: It specifies two or more columns, separated by commas, column_list must be enclosed in parentheses,
- *: It specifies that the query searches all full-text indexed columns in the table specified in the FROM clause for the given search condition. The columns in the CONTAINS clause must come from a single table that has a full-text index.
- <contains_search_condition>: It is a nvarchar type expression, if any other type of value is specified it will automatically convert it to nvarchar, it specifies the text to search for in column_name and the conditions for a match,
- { AND | & } | { AND NOT | &! } | { OR | | }: It specifies a logical operation between two contains search conditions
- { AND | & }: The logical AND operator Indicates that the two contains search conditions must be met for a match. The ampersand symbol (&) may be used instead of the AND keyword to represent the AND operator.
- { AND NOT | &! }: The logical AND NOT operator indicates that the second search condition must not be present for a match. The ampersand followed by the exclamation mark symbol (&!) may be used instead of the AND NOT keyword to represent the AND NOT operator
- { OR | | }: The logical OR operator Indicates that either of the two contains search conditions must be met for a match. The bar symbol (|) may be used instead of the OR keyword to represent the OR operator
Examples of SQL Contains
Consider an eCommerce shopping system with three tables’ product, customer, and order for performing practical examples of SQL contains
Customer Table
custid | custname | custaddress | custcity | custpincode | custemail | custcontactno |
---|---|---|---|---|---|---|
101 | reema | gansayamnagar,bhuj | bhuj | 395407 | reema@gmail.com | 45454545 |
102 | kiya | adajan,surat | surat | 395007 | kiya@gmail.com | 63636363 |
103 | priya | pal,adajan | surat | 395006 | priya@vnsgu.ac.in | 63696867 |
104 | leela | rajmarg,delhi | delhi | 320001 | leela@iitd.ac.in | 98989898 |
105 | lissa | bhatar,surat | surat | 395017 | lissa@vsngu.ac.in | 62626262 |
Product Table
productid | productname | category | tags | price | attribute |
---|---|---|---|---|---|
10001 | bluetooth | electronic | electronic,wired | 600 | blur color,wired,intel |
10002 | television | electronic | electronic,colored | 30000 | LED screen,unbrealable,balck |
10003 | iphone | electronic | electronic,apple | 25000 | 5.4-inch(13.7 cm diagonal) |
10004 | smart watch | electronic,watch | electronic, apple | 5000 | Apple Watch Series 7 |
Order Table
orderid | orderdate | productid | custid | quntity | shippingaddress | shippingdate | amount |
---|---|---|---|---|---|---|---|
111 | 02-02-1990 | 10001 | 101 | 2 | althan,canal road,surat | 12-02-1990 | 1500 |
112 | 03-03-1991 | 10002 | 102 | 1 | ambe nagar,delhi | 20-03-1991 | 28000 |
113 | 05-05-2000 | 10001 | 102 | 3 | bhatar,surat | 15-05-2000 | 2000 |
114 | 04-04-2000 | 10002 | 103 | 1 | bandra,mumbai | 10-04-2000 | 28000 |
115 | 05-04-2000 | 10003 | 104 | 2 | bhatar,mumbai | 10-04-2000 | 20000 |
Search for a single word in a single column
Example 1: Write SQL query to display those customer data whose address contains ‘surat’
select custid as 'Customer ID' , custname as 'Customer Name',custaddress as 'Address' , custcity as 'City',custpincode as 'Pincode',custemail as 'Email',custcontactno as 'Customer Contact No' from tblcustomer
where contains(custaddress,'surat');
- In the above query, SQL Contains is used to search for the word ‘surat’ in the full-text index column custaddress
- The first argument in contains function is the columnname custadress and the second argument is the word to be search
OUTPUT:
Search for a single word in multiple columns
Example 2: Write a SQL query to display all customer data that has the ‘surat’ word in the address or city column
select custid as 'Customer ID' , custname as 'Customer Name',custaddress as 'Address' , custcity as 'City',custpincode as 'Pincode',custemail as 'Email',custcontactno as 'Customer Contact No' from tblcustomer
where contains((custaddress,custcity),'surat');
- In the above query, SQL Contains is used to search for a word ‘surat’ in two columns custaddress and custcity
- The first argument in contains operator is two column names in the small bracket and the second argument is a word to be searched, SQL will search for a word in both columns and if found in either of the column value, it will be in the resulting recordset
OUTPUT:
Search for a word in all full-text indexed columns
Example 3: Write a SQL query to display products details which has a word ‘electronic’ in any of the column value
select productid as'ID',productname as 'Product Name',category as 'Product Category', tags as 'Product Tags',price as 'Price',attribute as 'Product Attribute' from tblproduct
where contains(*,'electronic');
- In the above query, SQL contains is used to search for a word 'electronic' in all column values
- The first argument of SQL Contain operator is the asterisk (*), it specified all searches in all full-text index columns, and the second argument is the ‘electronic’ word to be search
OUTPUT:
Search for two conditions with logical OR
Example 4: Write SQL query to display customer details who has ‘bharat’ or ‘surat’ word in the address value
select custid as 'Customer ID' , custname as 'Customer Name',custaddress as 'Address' , custcity as 'City',custpincode as 'Pincode',custemail as 'Email',custcontactno as 'Customer Contact No' from tblcustomer
where contains(custaddress,'"surat" or "bhatar"');
- In the above query, SQL contains is used to search for two words ‘surat’ and ‘bhatar’ in the custaddress column
- The First argument in the contains operator is the name of full-text indexed column name custaddress and the second argument is two words to be searched separated by logical operator OR, both words are enclosed in the double quotation mark
OUTPUT:
Search for two conditions with logical AND
Example 5: Write SQL query to display products information which are having ‘black’ and ‘LED’ as attribute value
select productid as'ID',productname as 'Product Name',category as 'Product Category', tags as 'Product Tags',price as 'Price',attribute as 'Product Attribute' from tblproduct
where contains(attribute,'"black" AND "LED"');
- In the above query, SQL Contains is used to search for two words with logical AND operator
- The first argument is full-text index column name attribute and the second argument is the two words ‘black’ and ‘LED’ enclosed in double quotation separated with AND, that means if any records have both words in the attribute column value then it will in the result set
OUTPUT:
Search for two conditions with logical AND NOT
Example 6: Write SQL query to display customer information whose address contains ‘surat’ word but not contains ‘pal’ word
select custid as 'Customer ID' , custname as 'Customer Name',custaddress as 'Address' , custcity as 'City',custpincode as 'Pincode',custemail as 'Email',custcontactno as 'Customer Contact No' from tblcustomer
where contains(custaddress,'"surat" and not "pal"');
OUTPUT:
SQL Contains with NEAR | ~
The NEAR|~ is the operator used with SQL Contains to predicate for the search text with a WHERE clause or the CONTAINSTABLE function, it is also used for exact or fuzzy searches of a search term in the text, a search term can be either a single word or a phrase delimited by double quotation marks
We must specify two search terms with NEAR, the word or phrase on each side of the NEAR or ~ operator must occur in a document for a match to be returned, A given search term can be either a single word or a phrase that is delimited by double quotation marks
Example 6: Write SQL query to search for a word electronic in all columns near around the word wired
select productid as'ID',productname as 'Product Name',category as 'Product Category', tags as 'Product Tags',price as 'Price',attribute as 'Product Attribute' from tblproduct
where contains(*,'NEAR((electronic, wired))');
- In the above query, SQL contains is used to find word electronic in any of the column values near to the word wired either before word wired or after the word wired
- The first argument in the SQL contains function is the * which indicated search in the all column values, the second argument is the NEAR operator with two arguments words to be search column and the second is the word which around the given word is to be searched
OUTPUT:
Summary
In this article, we have covered the SQL Contains which is used to do the searching operation for a word or phrase or nearby word on a full-text index, we have started with an overview of SQL Contains, the difference between SQL LIKE and SQL Contains, define the syntax of SQL Contains with an explanation of syntax argument, also covered practical examples of SQL Contains with the search of a word in a single column and multiple columns, a search of more than one word in single, multiple and all columns values, also covered example of more than one search condition combined with logical operators, and in the ending section we have covered the SQL NEAR operator used with SQL Contains
References