SQL CONTAINS Explained [Practical examples]

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,

Advertisement

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

Comparison of 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
Advertisement

 

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:

SQL CONTAINS Explained [Practical examples]

 

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:
SQL CONTAINS Explained [Practical examples]

 

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:

SQL CONTAINS Explained [Practical examples]

 

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:

SQL CONTAINS Explained [Practical examples]

 

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:

SQL CONTAINS Explained [Practical examples]

 

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 Explained [Practical examples]

 

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:

SQL CONTAINS Explained [Practical examples]

 

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)

 

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment

X