MySQL string contains query

This article will look into how to get the data from MySQL tables where a column contains a particular string. We will be illustrating the concept using various examples and different methods.

Table of Contents:-

• MySQL query string contains using ‘%’ wildcard
• MySQL query string contains using LOCATE
• MySQL query string contains using INSTR

Let us get started by preparing the sample data. We will be creating a table, employee_name_details, followed by inserting a few rows into it.

# create the table employee_name_details
CREATE TABLE employee_name_details(
  emp_id int  NOT NULL AUTO_INCREMENT,
  emp_firstName varchar(255) DEFAULT NULL,
  emp_lastName varchar(255) DEFAULT NULL,
  primary key(emp_id)
);
# insert rows to the table employee_name_details
INSERT INTO employee_name_details (emp_id,emp_firstName,emp_lastName) 
 VALUES(1,"Henry","Smith"),
 (2,"Richa","Johnson"),
 (3,"Veronica","Brown"),
 (4,"George","Jones"),
 (5,"Harsh","Garcia"),
 (6,"William","Jones"),
 (7,"Rebecca","Miller"),
 (8,"Paul","Davis"),
 (9,"Pualou","Miller");

Execute select * to view the data in the employee_name_details table.

SELECT * FROM employee_name_details;

Output:-

MySQL query string contains using ‘%’ wildcard

Example: Get all the rows from the table employee_name_details where the employee’s first name starts from ‘R.’ 

We will be using the ‘%’ wildcard for the solution. This ‘%’ wildcard is used for any set of characters. Observe the below explanation.

 SELECT * FROM employee_name_details WHERE emp_firstName LIKE 'R%' ;

Output:-

Example: Get all the rows from the table employee_name_details where the employee’s last name contains ‘ill.’

Observe the below query for the solution.

SELECT * FROM employee_name_details WHERE emp_lastName LIKE '%ill%' ;

Output:-

MySQL query string contains using LOCATE

Example: Get all the rows from the table employee_name_details where the employee’s last name contains ‘ill’

We will be using the LOCATE() function for the solution. LOCATE(substr, str) function returns the first occurrence of the substring passed in as parameters. Here substr is the substring passed in as the first argument, and str is the string passed in as the second argument. Observe the below query for the solution demanded by the above example.

SELECT * FROM employee_name_details WHERE LOCATE('ill', emp_lastName) > 0 ;

Output:-

Explanation:-

Here, the locate() function returns the first occurrence of the substring ‘ill’ present in any of the values of column emp_lastName. Since we are doing SELECT * along with locate() function, the query returns the rows with the ‘ill’ contained in the emp_lastName.

Let us say we want to retrieve the first occurrence of the substring ‘ill’ present in any of the values of column emp_lastName. We can use the below query.

SELECT LOCATE('ill', emp_lastName) FROM employee_name_details;

Output:-

As shown in the output, only two rows return the index of substring ‘ill’ present in the emp_lastName column.

MySQL query string contains using INSTR

Example: Get all the rows from the table employee_name_details where the employee’s last name contains ‘ill’

We will be using the INSTR() function for the solution. INSTR(str, substr) function returns the index of the first occurrence of the substring passed in as parameters. Here str is the string passed in as the first argument, and substr is the substring passed in as the second argument.

NOTE: Order of arguments get reversed in the INSTR() function compared to LOCATE() function.

Observe the below solution demanded by the above example.

SELECT * FROM employee_name_details WHERE INSTR(emp_lastName , 'ill') > 0

Output:-

Explanation:-

Here, the instr() function returns the first occurrence of the substring ‘ill’ present in any of the values of column emp_lastName. Since we are doing SELECT * along with instr() function, the query returns the rows with the ‘ill’ contained in the emp_lastName.

Let us say we just want to retrieve the first occurrence of the substring ‘ill’ present in any of the values of column emp_lastName. We can use the below query.

SELECT  INSTR(emp_lastName, 'ill') FROM employee_name_details;

Output:-

READ MORE:

• MySQL get data by string length
• Mysql update set multiple rows
• Mysql update column with value from another table
• MySQL Update with Inner Join
• MySQL Insert with Join
• MySQL: Insert in a loop

We hope this article helped you get data from MySQL tables where a particular string is contained in column values. Good Luck!!!