How to Search Occurrences of String in MySQL Database

1. Method 1: Search Entire Database Using the phpMyAdmin
2. Method 2: Search the Entire Database Using the MySQL Workbench
3. Conclusion

As a database administrator, there are some situations and circumstances where you have to search the whole database to find the occurrences of a pattern or a string.

For example, how many employees use a Gmail account in your office, or you want to know a certain age from the staff’s table to know their retirement date, etc.

We will explore Graphical User Interface-based methods to search the whole database, including searching in MySQL Workbench and phpMyAdmin. However, you can also create SQL commands and procedures.

For this article, we are focusing on GUI options to search.

Method 1: Search Entire Database Using the phpMyAdmin

Here, we will see how we can search all tables in a database using phpMyAdmin. You can easily find the occurrences of your required string by following the given steps.

Create Database and Tables

We have created a database named person and two tables named student and teacher.

Populate Tables and Check Data

You can insert data into each table by using the following code.

#insert into student table
INSERT INTO student(ID, FIRST_NAME, LAST_NAME,GENDER, EMAIL)
VALUES
(1,'Shaajeel', 'Daniel', 'Male','shajeeld@gmail.com'),
(2,'Nayya', 'Preston', 'Female','npreston@yahoo.com'),
(3,'James', 'Robert', 'Male','james@yahoo.com'),
(4,'Jennifer', 'John', 'Female','jennifer09@hotmail.com'),
(5,'Sarah', 'Paul', 'Female','sarahpaul011@yahoo.com'),
(6,'Karen', 'Donald','Female','dkaren@gmail.com');

#insert into teacher table
INSERT INTO teacher(ID, FIRST_NAME, LAST_NAME,GENDER, EMAIL)
VALUES
(1,'Thomas', 'Christopher','Male','thomasC098@hotmail.com'),
(2,'Lisa', 'Mark', 'Female','lisamark@gmail.com'),
(3,'Anthony', 'Richard', 'Male','anthony044@yahoo.com'),
(4,'Matthew', 'Charles', 'Male','matthewcharles@gmail.com')
(5,'Kiren', 'Donald','Female','dkiren@gmail.com');

You can see the data inserted using the following SELECT queries.

SELECT * FROM `teacher`;

SELECT * FROM `student`;

Search String

To search the string,

• Select the database first (see box number 1).
• Click on the Search tab and write the string, pattern, or expression you want to search (see box number 2).
• Select the searching criteria whether you are looking for the exact match or something else; see the red box number 3.

We are looking for the exact match as a substring for this tutorial. Select the tables to search for red box number 4 and click on the GO button in the bottom right corner.

The following screen capture will show the count of found matches for each table. You can click on the Browse button to see the respective complete record (row).

Method 2: Search the Entire Database Using the MySQL Workbench

If you are using MySQL Workbench, you can search the whole database for a particular string or pattern using Graphical User Interface.

First, select all the tables you want to search (see red box 1). Remember, if you do not select a table, the search operation will not be performed.

Click on the Database Menu and select Search Table Data (Database Menu -> Search Table Data). As we are searching for the exact match for a substring, we selected CONTAINS (see red box number 3), you can select as per your needs.

Now, you can write the string or expression you want to search (see red box number 4).

You can tell the maximum number of matches per table and a complete database. You can also specify whether you want to search all types of columns or not and then click Start Search.

It will show the output, including schema, table name, primary key, column name (where match found), and its value (see red box number 6).

Conclusion

After learning about two methods for searching the whole database, which include searching via MySQL Workbench and phpMyAdmin, we have concluded that GUI options made the job easy for database administrators, database programmers, and architects. Although there are SQL queries behind the scenes, we can use GUI options to do more efficiently and quickly.