Click here to Skip to main content
15,442,473 members
Articles / Database Development
Technical Blog
Posted 6 Mar 2022

Tagged as

Stats

2.3K views
2 bookmarked

Contains in SQL

Rate me:
Please Sign up or sign in to vote.
3.00/5 (1 vote)
6 Mar 2022CPOL4 min read
Sooner or later, you want to know when a column contains in SQL another value.  In this article we’ll go over several ways you can test to see whether one value is contained within an another.

What makes this problem different, is that we aren’t looking for an exact match, rather, we’re looking for a value within another. Consider the production description:

 "Mountain Bike with lightweight frame yet rugged enough to tackle the toughest trails."

What can we use in SQL to test whether the description contains the word “frame?”

Contains in SQL Video Lesson

Before we dig into the details, don’t forget to check out the accompanying video. I go over all the example there to help you understand what is going on with the queries.

Today we’ll go over five ways you can do this test. At the end, I’ll let you know which one I like best, but be sure to check out every approach, as you get to learn more SQL in the process.

Use LIKE and Pattern Matching

You can use the LIKE operator to match against a pattern. You can use wildcard characters to match for word and characters within a column.

To find all Product Names containing ‘Frame’ we could use:

SQL
select ProductID, ProductNumber, Name ProductName
from Production.Product
where Name like '%frame%'

Check out the pattern. The percent signs (%) instruct the pattern to match zero or more characters. In English, this pattern says, “look for ‘frame' don’t worry what comes before or after it.”

CHARINDEX() to Find Position of Word

CHARINDEX() is a TSLQ function that returns the starting position of a character expression in another character expression. We can use this to help us figure out whether our SQL columns contain the searched text.

Continuing with our example if we’re looking for “Frame” we can use this query:

select ProductID, ProductNumber, Name ProductName
from Production.Product
where CHARINDEX('frame', name) > 0

This works as it CHARINDEX() returns a values greater than 0 if “frame” is found within the column name.  If the value isn’t found, zero is returned.

PATINDEX() To Find Position of Pattern

The PATINDEX() TSQL function is very similar to CHARINDEX(); however, it can take a pattern for matching. This is  how LIKE does its matching. They use the same pattern matching ideas.

To find “frame” within Product.Name use this query along with PATINDEX():

SQL
select ProductID, ProductNumber, Name ProductName
from Production.Product
where PATINDEX('%frame%', name) > 0

To learn more about PATINDEX check out my article SQL PATINDEX() Function.

Interesting Use of IN with STRING_SPLIT()

You may think you can use the IN clause to find Frame, and in a way you’re partially correct.  But one thing is for sure, this query will not work!

SQL
select ProductID, ProductNumber, Name ProductName
from Production.Product
where name in ('frame')

The problem is the filter does first break name into word before comparing to “frame.”  Luckily there is a SQL function that addresses this issue.

To get around this dilemma we’ll use STRING_SPLIT(). Unlike other functions, which output a single value, STRING_SPLIT() returns a table.

Is this example you can see where we split the string “Hail to the Victors” into four separate words.

contains in sql STRING_SPLIT example

Knowing we get a table returned, let’s write a subquery and use the IN operator.  We’ll test whether STRING_SPLIT() includes “frame”  within its results:

SQL
select ProductID, ProductNumber, Name ProductName
from Production.Product
where 'frame' in (select value from STRING_SPLIT(name, ' ') )

There are couple of steps taking place:

  1. STRING_SPLIT() returns a table of words within the Product.Name
  2. The subquery test to see whether “frame” is within this table, if so the IN comparison returns TRUE.

If this one seems confusing, then be sure to watch my YouTube Video, as I go this this example in detail for you to understand.

Full Text Search with CONTAINS() in SQL

The final solution we’ll talk about is CONTAINS(). It return true if one word is contained within another phrase, such as a our column Product.Name.

It seems like the most obvious choice to use, until you realize it requires some upfront work.

Since CONTAINS relys on SQL’s full text capabilities, you’ll need to ensure those features are installed in the DBMS.  Also, inorder to use on a column, the column requires a FULLTEXT index.

Here is how you can set up the index:

SQL
create fulltext catalog ft as default
create fulltext index on Production.Product(name) key index ak_product_name with stoplist=system

Once that is complete, the query is straight forward:

SQL
select ProductID, ProductNumber, Name ProductName
from Production.Product
where CONTAINS(name, 'frame')

This technique has potential, especially if you need to search massive full text such as level depositions.  If this is something you’re looking to do, then check out Microsoft’s Full Text Search article.

Conclusion

Out of all the techniques listed the one I would most likely use is is LIKE. When it comes to queries it is easy to setup and its pattern matching capabilities are handy.

With that said, don’t discount the other techniques. Thought I would use them in a query all the time, the functions are handy and I do use them from time to time.

This article was originally posted at https://www.essentialsql.com/contains-in-sql

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Easy Computer Academy, LLC
United States United States
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.

I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.

It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.

I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.

Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.

It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.

Having video, pictures, and text really help to reinforce the point and enable learning.

And now I want to help you get the same results.

The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/

Comments and Discussions

 
-- There are no messages in this forum --