SQL: 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:
SELECT * FROM Customers WHERE CustomerName LIKE '%joe%'SELECT * FROM Customers WHERE CHARINDEX('joe', CustomerName, 0) > 0SELECT * FROM Customers WHERE PATINDEX('jo%', CustomerName, 0) > 0How 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' |
Case-insensitive substring search
If you are dealing with a case-sensitive database, make sure to do the substring comparisons against the normalized string, for example:
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:
SELECT * FROM Customers WHERE CHARINDEX('joe', CustomerName, 0) > 0
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
SELECT * FROM Customers WHERE CHARINDEX('joe', CustomerName COLLATE Latin1_General_CI_AS, 0) > 03. 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:
SELECT * FROM Customers WHERE PATINDEX('jo%', CustomerName, 0) > 0PATINDEX 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:
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. |
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:
- Connect to your SQL Server instance using SQL Server Management Studio (SSMS) or another tool.
- Run the following query to check the collation of the database:
SELECT DATABASEPROPERTYEX('database_name', 'Collation');- The query will return the collation of the database, for example, 'SQL_Latin1_General_CP1_CI_AS'.
- 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.
- If you want to check the collation of all the databases in the SQL Server instance, you can use the following query:
SELECT name, collation_name FROM sys.databases
Josip Miskovic is a software developer at Americaneagle.com. Josip has 10+ years in experience in developing web applications, mobile apps, and games.
Read more posts →- Quick Example: Check if the String contains a Substring
- How to check if the string contains a substring in SQL?
- 1. Use the LIKE operator
- Case-insensitive substring search
- 2. CHARINDEX - Find the position index of a substring
- Case-insensitive substring search
- 3. PATINDEX - Find the position index of a substring pattern
- How to find the location of a substring within a string?
- Case-sensitive substring match
- How to check if SQL Server is case sensitive?
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.