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:
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:
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:
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:
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:
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.
WHERE POSITION('apple' IN product_name) > 0;
This works very similarly to the CHARINDEX() function in SQL Server.
Conclusion
In this tutorial, we learned how we can use the various SQL methods and techniques to check whether a string contains a specified substring.
