SQL Standard

SQL String Contains

String operations is a common task in the world of development. When it comes to databases, you will encounter yourself needing to search or manipulate the text information.

One common operation is searching for a specific substring or pattern within a text value, expression, or column. This is also known as “string contains”.

String contains mainly comes up when you need to filter or retrieve the data that follows a specific text pattern.

In this tutorial, we will explore the various methods and techniques that you can use to search whether a string contains another substring. It is good to keep in mind that each database provides different methods for the same operation. It is therefore good to consult the document for your desired database engine to learn more.

General Syntax:

Before we dive into the various methods, we can attempt to establish a common syntax that can summarize how the string contains the operation works in SQL.

Take a look at the following example:

SELECT * FROM table_name

WHERE column_name CONTAINS 'substring_to_search';

You will notice that we use the WHERE clause to establish a search based on the text that matches the specified pattern.

The CONTAINS on this case varies on the target database engine. For example, you may need to replace it with functions such as EXTRACT, SUBSTRING, and more.

Again, this heavily depends on your database engine.

Method 1: LIKE Operator

The first and most common operator that we can use to check whether a given string contains another substring is the LIKE operator. This operator allows us to perform the pattern-based matching using the wildcard characters.

Take for example the following code that demonstrates how to use the “%” wildcard character to search whether a string contains another:

SELECT * FROM products

WHERE product_name LIKE '%apple%';

This query returns all rows from the “products” table where the “product_name” column contains the word “apple” anywhere within the string.

Another example is using the “_” as the wildcard character. Take a look at the following code:

SELECT * FROM products

WHERE product_name LIKE 'Ap_le';

In this case, the query returns the rows where the “product_name” column contains any single character in the place of the “_” character to. An example is something like “Apple”.

Method 2: CHARINDEX()

In SQL Server, we have access to the CHARINDEX() function that allows us to find the position of a substring within a given string. If the result is greater than 0, it means that the substring is within the specified string.

An example is as follows:

SELECT * FROM products

WHERE CHARINDEX('apple', product_name) > 0;

This query selects the rows from the “products” table where the “product_name” column contains the word “apple”.

Oracle also supports this function but it is under the name INSTR() function as shown in the following example:

SELECT * FROM products

WHERE INSTR(product_name, 'apple') > 0;

This should return the matching substring.

Method 3: POSITION()

In PostgreSQL, we can use the POSITION() function to check for the existence of a substring within another string.

SELECT * FROM products

WHERE POSITION('apple' IN product_name) > 0;

This works very similarly to the CHARINDEX() function in SQL Server.


In this tutorial, we learned how we can use the various SQL methods and techniques to check whether a string contains a specified substring.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list