How to Select Rows in SQL Where a Column Contains Specific Words Baeldung on SQL (2024)

1. Introduction

In this tutorial, we’ll look at querying rows that contain given words or phrases in SQL.

We tested our examples on MS SQL Server 2022, PostgreSQL 14, and MySQL 8 databases. However, most methods we discuss should be available in other versions of these SQL implementations and other SQL dialects.

2. Problem Statement

In many SQL operations, it’s necessary to identify rows where a specific word or phrase exists within a particular column. This capability is crucial for filtering data and retrieving relevant information for the analysis.

Equality checks are not enough here, as we need to identify rows containing a specific word, even if it’s not an exact match.

3. Model

Before writing the SQL queries for this task, let’s set up the data we’ll use.

Let’s define a table for which we’ll write queries:

CREATE TABLE Product ( product_id INTEGER PRIMARY KEY, name VARCHAR(50), description VARCHAR(100));

Next, let’s insert some sample data into it:

INSERT INTO Product VALUES (1, 'RitterSport AlpenMilch', 'Milk Chocolate');INSERT INTO Product VALUES (2, 'Milka Dark Milk', 'Dark Chocolate');INSERT INTO Product VALUES (3, 'Marabou Not Choklad', 'Milk Chocolate With Nuts');INSERT INTO Product VALUES (4, 'Mentos Full Fruit', 'Sugarfree Chewing Gums');INSERT INTO Product VALUES (5, 'Lays', 'Potato Chips');INSERT INTO Product VALUES (6, 'Milka Marabou', 'Milky Darks Chocolate');

We’ll base all our examples on finding the rows that contain the terms Milk or Dark in the description column.

4. Using LIKE

We can use the LIKE operator to perform a wildcard search on the columns:

SELECT * FROM Product WHERE description LIKE '%Milk%' OR description LIKE '%Dark%';

The LIKE operator combined with % on both sides of the search terms allows for flexible searching. This means the target termscan appear anywhere within the description column, not just at the beginning or end. The LIKE operator is part of the SQL standard. It’s available in all the databases we consider in this article.

Here is the result after executing the above query:

The query successfully retrieved the rows where the description column contains Milk or Dark.

It’s important to note that this query performs a case-sensitive search. If the data might contain variations in capitalization, we can modify the query to achieve case-insensitive searching:

SELECT * FROM Product WHERE LOWER(description) LIKE LOWER('%Milk%') OR LOWER(description) LIKE LOWER('%Dark%');

Here, we use the LOWER() function for both the column and the search term to perform a case-insensitive search.

5. Using Other Pattern Matching Operators

Apart from LIKE, which is a part of the SQL standard, different SQL dialects have different pattern-matching operators.

5.1. PostgreSQL

In PostgreSQL, we can use the ~ operator for pattern matching:

SELECT * FROM Product WHERE description ~ 'Milk' OR description ~ 'Dark'

We can use ~* instead of ~ if we want a case-insensitive search.

Similarly, ILIKE works the same as LIKE but is case-insensitive by default. It’s specific to PostgreSQL and not a part of the SQL standard.

5.2. MySQL

Additionally, we can use pattern matching using the REGEXP operator in MySQL:

SELECT * FROM Product WHERE description REGEXP 'Milk' OR description REGEXP 'Dark' 

Although these operators offer enhanced pattern-matching capabilities, their performance is generally inferior to that of the simple LIKE operator.

5.3. MS SQL

Unlike MySQL and PostgreSQL, MS SQL lacks dedicated pattern-matching operators. However, the LIKE operator in MS SQL offers limited pattern-matching capabilities, supported by regex-like operators such as [] and[^].

6. Using Built-in Functions

An alternative way to achieve this functionality is by using built-in functions specific to individual databases.

6.1. PostgreSQL and MySQL

In PostgreSQL and MySQL databases, we can use POSITION() to search for a word:

SELECT * FROM Product WHERE POSITION('Milk' IN description) > 0 OR POSITION('Dark' IN description) > 0;

The POSITION() function returns the index of the provided substring in the given column and 0 if it doesn’t exist.

Additionally, we can transform the query into a case-insensitive search by using the LOWER() function:

SELECT * FROM Product WHERE POSITION('milk' IN LOWER(description)) > 0 OR POSITION('dark' IN LOWER(description)) > 0;

6.2. MS SQL

In MS SQL, we can utilize the CHARINDEX() function to search for a substring’s position:

SELECT * FROM ProductWHERE CHARINDEX('Milk', description) > 0 OR CHARINDEX('Dark', description) > 0;

Like POSITION(), CHARINDEX() returns the 1-based index of the search substring or 0 if it isn’t in the string.

7. Comparison

Let’s check the availability of the discussed options across various databases:

Although the LIKE operator offers search flexibility searching, the specific functions are more efficient for huge datasets as they are optimized for performance. However, built-in functions make it challenging to use generic queries across databases due to variations in syntax and functionality.

Typically, PostgreSQL is case-sensitive unless specifically configured otherwise, whereas MySQL and MS SQL Server generally support case insensitivity by default.

8. Conclusion

In this tutorial, we explored different options to filter rows that contain specific words or phrases. Combined with wildcards, the LIKE operator offers a powerful and flexible approach to pattern matching and is available in all SQL implementations. Pattern-matching operators available in each database enable advanced search operations. Furthermore, we can use database-specific functions like POSITION() and CHARINDEX() to check for the position of the target term in a string.

The scripts used in the article are available over on GitHub.