SQL Where String Contains Substring

In this tutorial, you will find out many ways to find substrings in SQL regardless of the DBMS. We will cover conventional operators like ‘LIKE’, ‘CONTAINS’, ‘CHARINDEX’, and many others. So buckle up and get started or just scroll to find the exact way to use find exactly where strings contain substring

Watch The Video on How to Use Pattern Matching in SQL

What is the LIKE Operator?

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

There are two wildcards often used in conjunction with the LIKE operator:

• %: Represents zero, one, or multiple characters.
• _: Represents a single character.

Examples:

SELECT * FROM employees WHERE first_name LIKE 'A%';

This would find any employees that contain A at the start of their first name

Finding Any Records with ‘an’ Anywhere in the Name

SELECT * FROM employees WHERE first_name LIKE '%an%';

This would retrieve all rows where first_name has ‘an’ anywhere in the string.

Finding Records with Exactly Five Characters

SELECT * FROM employees WHERE first_name LIKE '_____';

This returns all rows where first_name is exactly five characters long.

NOT LIKE:

In cases where you want to find records that do not match a particular pattern, you can use the NOT LIKE operator.

SELECT * FROM employees WHERE first_name NOT LIKE 'A%';

Alternatives to LIKE in SQL:

• Regular Expressions: Some databases support regular expression matching, which can provide more flexibility than the LIKE operator.
• Full-Text Search: For extensive text searching, some databases offer full-text search capabilities which can be more efficient and offer more features than a simple LIKE query.

SQL Server Full-Text Search: In Microsoft SQL Server, CONTAINS is a keyword used in conjunction with full-text search. It allows for more advanced textual searches in full-text indexed columns. For instance:

SELECT * FROM products WHERE CONTAINS(product_description, 'laptop');

Standard Methods to Find Substrings:

• Usage: Functions like CHARINDEX, INSTR, LOCATE, POSITION, and STRPOS (depending on the SQL database) are used to locate the position of one string within another. These are standard string manipulation functions available in most SQL databases.
• Advantage: They are straightforward, do not require any additional indexing, and are excellent for simple substring searches.

CHARINDEX (SQL Server)

In SQL Server, the CHARINDEX function can be used to get the starting position of a substring within a string. If the substring is not found, CHARINDEX returns 0.

SELECT CHARINDEX('substring', column_name)
FROM table_name;

INSTR (Oracle, MySQL, SQLite)

For Oracle, MySQL, and SQLite, the INSTR function serves a similar purpose as CHARINDEX in SQL Server. It returns the position of the first occurrence of a substring in a string, or 0 if not found.

SELECT INSTR(column_name, 'substring')
FROM table_name;

POSITION and STRPOS (PostgreSQL)

In PostgreSQL, you can use the POSITION or STRPOS functions to find the location of a substring.

SELECT POSITION('substring' IN column_name)
FROM table_name;