SQL CONTAINS Explained [Practical examples]

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

CONTAINS ( {   column_name | ( column_list )   | * }  , '<contains_search_condition>'  [ { <AND> | <AND NOT> | <OR> } ]   
        <contains_search_condition> [ ...n ] );

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

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

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

CONTAINS (Transact-SQL)