How to Search Rows to Find Substrings in MySQL

1. Method 1: Find a String/Substring Using the % Wildcard
2. Method 2: Find a String/Substring Using the LOCATE() Function
3. Conclusions

In some situations, you have to search a string or substring in a table.

For example, you want to know how many Gmail users in the employee table are. Another example is to find all firstname starting with Je in the employee table.

Here, searching for a string or substring comes in the picture, and there are different ways to search. POSITION() for standard SQL, LOCATE() and % wildcard for MySQL, and INSTR() for Oracle.

We will learn the MySQL-supported methods and observe which one is the best and easy way to search rows to find substrings in MySQL. You can look at all of them and then decide which method is best in your case.

Method 1: Find a String/Substring Using the % Wildcard

% Wildcard is used to substitute one or multiple characters in a string. This wildcard is used with the LIKE operator, and the LIKE operator is used in the WHERE clause.

To practice, we created a new table named employee. You can create the same and populate it with data using the sample code given below.

In this way, we will be on the same page while learning.

#Create employee table
CREATE TABLE employee(
id int not null primary key,
firstname varchar(60) not null,
lastname varchar(60) not null,
gender varchar(30) not null,
city varchar(40) not null,
email varchar(60) not null
);

#Insert data into employee table.
INSERT INTO employee VALUES 
    (1,'James','Robert','Male','London','jrober@gmail.com'),
    (2,'Mary','Patricia','Female','Berlin','patricia@gmail.com'),
    (3,'John','Michael','Male','Paris','johnmichael@yahoo.com'),
    (4,'Jennifer','Linda','Female','Lisbon','jennifer@hotmail.com'),
    (5,'William','David','Male','Rome','wdwilliam@gmail.com'),
    (6,'Elizabeth','Barbara','Female','Dublin','elibarbara011@yahoo.com'),
    (7,'Richard','Joseph','Male','Oslo', 'richard@gmail.com'),
    (8,'Susan','Jessica','Female','Hamburg','susan01@yahoo.com'),
    (9,'Thomas','Charles','Male','Texas', 'thomas.charles@hotmail.com'),
    (10,'Karen','Nancy','Female','Washington','karenofficial@gmail.com');

SELECT * FROM employee;

Output:

Let’s understand considering different scenarios for searching rows to find a string or substring using % wildcard.

Scenario 1: Find all Gmail users. Meaning, we have to find a substring in the email.

SELECT firstname, lastname from employee where email LIKE '%@gmail.com';

The above SQL query will select firstname and lastname from the employee table whose email string contains gmail.com.

Output:

Scenario 2: Select those last names that end with the letter a. Here, we will find a string, the lastname.

SELECT lastname FROM employee WHERE lastname LIKE '%a';

Output:

Scenario 3: Let us find the city names starting with L, ending with n, and having four characters between. One underscore (_) is for one character.

SELECT city FROM employee WHERE city LIKE 'L____n';

Output:

You can learn more wildcard characters here.

Method 2: Find a String/Substring Using the LOCATE() Function

LOCATE function returns the substring’s first occurrence in a string. It returns 0 if unable to find substring within the original string.

This function does a case-insensitive search which means HELLO, and hello are the same for the LOCATE function. It is used with the following syntax.

LOCATE(substring, string, start)

In the syntax given above, the first two parameters named substring and string are required, but the third parameter named start is optional.

You may have a question about how the output will look like? Let’s understand via the following image.

SELECT LOCATE("hotmail", email) FROM employee;

Output:

Conclusions

We concluded that different database platforms support different methods. LOCATE(), and wildcard is compatible with MySQL.

We learned MySQL supported ways to search a string or substring with the help of examples. And observe the output to understand.