How to perform substring search using SQL
Substring searching allows us to search for specific parts of longer strings. This often makes it easier to search for rows within our database.
There are several ways to perform a substring search on a table within our database using
Using %
Using the % symbol, we can define a basic expression of the substring for which we are searching.
The % symbol basically refers to the remaining characters of the string.
Let’s look at a few examples:
SELECT Username
FROM USERS
WHERE Username like 'A%'
The above query returns a table that contains all the usernames starting with A.
Similarly, the following query returns a table that contains all the usernames ending with A:
SELECT Username
FROM USERS
WHERE Username like '%A'
To search for a substring in between the longer string, we must simply append % at the start and end of the substring.
The following query returns a table containing all the usernames with an A in between.
SELECT Username
FROM USERS
WHERE Username like '%A%'
We can also get specific when searching for substrings. The following query returns all strings that contain I as the third character:
SELECT Username
FROM USERS
WHERE Username like '--I%'
We can use the % sign in various ways to look for a substring within our database.
Using CHARINDEX()
The SQL CHARINDEX() function looks for a substring that starts from a specified location inside a string.
This function returns the position of the substring found in the searched string, or zero if the substring is not found.
Indexing starts from 1. Thus, an output of 0 represents that the substring was not found.
The following is the function prototype:
CHARINDEX(_substring, _string, _start)
The parameter _start is the starting index of the search. This parameter is optional.
Let’s look at an example:
SELECT CHARINDEX('man', 'Superman') AS StartPosition;
The above query returns 6, as the substring ‘man’ exists within the longer string ‘Superman’ starting from index 6.
Using SUBSTRING()
The SUBSTRING() function in SQL extracts a substring from a string, starting and ending at a specified position.
The following is the function prototype:
SUBSTRING(column_name, _start, length_of_substring)
To use the SUBSTRING() function for substring search, we call the function for the specific column and equate the output to the required substring.
Here’s an example:
SELECT Username
FROM USERS
WHERE SUBSTRING(Username, 1, 2) = 'AL'
The above query returns a table containing usernames (since we put 1 as the starting index) that contain the substring ‘AL’.
