SQL: Check if the String contains a Substring

A thumbnail showing SQL query to check if the string contains a substring.

Quick Example: Check if the String contains a Substring

Two simple SQL query examples to check if the String contains a Substring:

sql
SELECT * FROM Customers WHERE CustomerName LIKE '%joe%'
sql
SELECT * FROM Customers WHERE CHARINDEX('joe', CustomerName, 0) > 0
sql
SELECT * FROM Customers WHERE PATINDEX('jo%', CustomerName, 0) > 0

How to check if the string contains a substring in SQL?

In SQL Server, there are 3 main ways to check if a string contains a substring:

1. Use the LIKE operator

The LIKE operator is used in SQL to match a string with a pattern.

We can use the LIKE operator to check if the string contains a substring in several ways:

Character Description Example
a% Matches any substring starting with character a SELECT * FROM Customers WHERE CustomerName LIKE 'a%'
%a Matches any substring ending with character a SELECT * FROM Customers WHERE CustomerName LIKE '%a'
%joe% Matches any substring containing the string joe SELECT * FROM Customers WHERE CustomerName LIKE '%joe%'
_ Matches any single character SELECT * FROM Customers WHERE CustomerName LIKE '_oe'

If you are dealing with a case-sensitive database, make sure to do the substring comparisons against the normalized string, for example:

sql
SELECT * FROM Customers WHERE LOWER(CustomerName) LIKE '%joe%'

The SQL query above performs a case-insensitive check if a string CustomerName contains a substring joe.

2. CHARINDEX - Find the position index of a substring

In SQL Server, the CHARINDEX function returns the index position of a substring within a string. If the returned value is greater than 0, it means our string contains a substring:

sql
SELECT * FROM Customers WHERE CHARINDEX('joe', CustomerName, 0) > 0
SQL Query to check if string contains a substring in SQL Server

Keep in mind: In C, C++, and C#, the first element in a string has index position 0. However, in SQL, the first character in a string is at position 1. So when using functions like CHARINDEX to find the position of a substring within a string, a return value of 0 indicates that the substring was not found, not that it was found at the first position in the string.

Case-insensitive substring search

sql
SELECT * FROM Customers WHERE CHARINDEX('joe', CustomerName COLLATE Latin1_General_CI_AS, 0) > 0

3. PATINDEX - Find the position index of a substring pattern

PATINDEX is another function that helps you find a substring within a string. PATINDEX uses regular expressions to search for the substring, while CHARINDEX uses simple string matching:

sql
SELECT * FROM Customers WHERE PATINDEX('jo%', CustomerName, 0) > 0

PATINDEX is more flexible than CHARINDEX since it uses regular expressions for more complex pattern matching. However, it is not as fast as CHARINDEX since regular expressions require more processing power.

How to find the location of a substring within a string?

In SQL Server, you can use the CHARINDEX function to find the location of a substring within a string:

sql
CHARINDEX(substring, string, start_location)

The table below explains the parameters of the CHARINDEX function:

Parameter Description
substring The substring that you want to find the location of
string The string that you want to search in
start_location

An optional parameter that indicates the position in the string where the search should start. The default value is 1, meaning the search starts at the beginning of the string.

SQL Query to check substring position index in SQL Server

Case-sensitive substring match

Most of the SQL database management systems, including SQL Server, are case-insensitive by default, meaning that the LIKE operator will match substrings regardless of their case.

However, it's also possible to configure a database to be case-sensitive, in which case the LIKE operator would only match substrings that have the same case as the search pattern.

How to check if SQL Server is case sensitive?

To check if a SQL Server database is case-sensitive, you can use the following method:

  1. Connect to your SQL Server instance using SQL Server Management Studio (SSMS) or another tool.
  2. Run the following query to check the collation of the database:
sql
SELECT DATABASEPROPERTYEX('database_name', 'Collation');
Replace 'database_name' with the name of your database.
  1. The query will return the collation of the database, for example, 'SQL_Latin1_General_CP1_CI_AS'.
  2. If the collation ends with 'CI_AS', it means the database is case-insensitive. If it ends with '_BIN' or 'CS_AS', it means the database is case-sensitive.
  3. If you want to check the collation of all the databases in the SQL Server instance, you can use the following query:
sql
SELECT name, collation_name FROM sys.databases
SQL Query to get database case sensitivity
Published on:
Download Free Software Developer Career Guide

I've used these principles to increase my earnings by 63% in two years. So can you.

Dive into my 7 actionable steps to elevate your career.