JIYIK CN >

In this article, we aim to explore different ways to check the strings contained in a MySQL table.

We will introduce the following technologies in MySQL.

• INSTRfunction
• LOCATEfunction
• LIKEOperators

Before we get started, however, we create a dummy dataset to work with. Here, we create a table, student_details, and a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table and rows in it containing the first and last name of the students. To see the entries in the data, we use the following code.

SELECT * FROM student_details;

The above code will give the following output.

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Let's aim to find all Parkerstudents whose last name contains the word .

Use the LOCATE function in MySQL to check if a string contains certain data in MySQL

The locate function in MySQL generally takes 2 parameters, for example LOCATE(substr, str). Here, substris the substring passed as the first parameter, and stris the string passed as the second parameter. LOCATEThe output of the function is the first row where the string passed as the parameter appears. To see this function in action, look at the code below.

-- finding the word 'Park' from the table where the last name of the student is Park.
SELECT * FROM student_details WHERE LOCATE('Park', stu_lastName) > 0 ;

The above code will give the following output:

stu_id	stu_firstName	stu_lastName
6	      Sachin	      Parker

Use the INSTR function in MySQL to check if a string in MySQL contains certain data

Similar to the LOCATE function, the INSTR function INSTR(str, substr) accepts 2 parameters. However, this function returns the index value of the first occurrence of the string within the substring passed in as a parameter. Here, strthe string is passed in as the first parameter, and substrthe substring is passed in as the second parameter. To see this function in action, look at the code below.

-- finding the word 'Park' from the table where the last name of the student is Park.
SELECT * FROM student_details WHERE INSTR(stu_lastName , 'Parker') > 0;

The above code will give the following output.

stu_id	stu_firstName	stu_lastName
6	      Sachin	      Parker

Check if a string in MySQL contains certain data using the LIKE operator in MySQL

Another alternative way to find if a string exists in the data is to use LIKE. This operator WHEREis used with the clause to find a specific string. To see this technique in action, look at the code below.

-- finding the word 'Park' from the table where the last name of the student is Parker.
SELECT * FROM student_details WHERE stu_lastName LIKE 'Parker' ;

The above code will again give the following output.

stu_id	stu_firstName	stu_lastName
6	      Sachin	      Parker

Additionally, %, also known as a wildcard character, is also used with the LIKE operator. As the name implies, this wildcard character represents none, one, or more characters. To see this wildcard character in action, look at the following code.

-- finding the student with last name ending in 'arker' from the table.
SELECT * FROM student_details WHERE stu_lastName LIKE '%arker' ;

The above code will again give the following output.

stu_id	stu_firstName	stu_lastName
6	      Sachin	      Parker

So, with the help of above three techniques, we can effectively find the presence of a string from the table.