JIYIK CN >

In some cases, you must search for a string or substring in a table.

For example, you want to know employeehow many Gmail users there are in the table. Another example is employeeto find all Jethe names starting with in the table firstname.

Here, searching for a string or substring comes into the picture, and there are different ways to search for it: POSITION()for standard SQL, LOCATE()and with %wildcards for MySQL, and with String INSTR()for Oracle.

We will learn the methods supported by MySQL and observe which one is the best and simple way to search rows for a substring in MySQL. You can look at all these methods and decide which one is best for your situation.

Method 1: Use %wildcards to find strings/substrings

%A wildcard character is used to replace one or more characters in a string. This wildcard character LIKEis used with the operator, LIKEwhich is used in WHEREa clause.

For the purpose of practicing, we have created a employeenew table named . You can create it and fill it with data using the sample code given below.

This way, we will all be in step as we learn.

#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 us understand %different scenarios considering searching lines for a string or a substring using wildcard characters.

Scenario 1: Find all Gmailusers. That means, we have to emailfind a substring in .

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

The above SQL query will select and from the table emailwhere the string gmail.comcontains .employeefirstnamelastname

Output:

Scenario 2: Select alast names ending with letters . Here we will find a string lastname.

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

Output:

Scenario 3: Let's find names that Lstart with and nend with city, with four characters in between. An underscore ( _) represents one character.

SELECT city FROM employee WHERE city LIKE 'L____n';

Output:

You can learn more about wildcards here.

Method 2: Use LOCATE()the function to find a string/substring

LOCATEThe function returns the first occurrence of the substring in the string. If the substring cannot be found in the original string, it returns 0.

This function performs a case-insensitive search, which means HELLO, same helloas LOCATEthe 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 startthe third parameter named is optional.

You might be wondering how the output will look like? Let’s understand with the following image.

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

Output:

in conclusion

We conclude that different database platforms support different methods. LOCATE(),Wildcards are compatible with MySQL.

We have seen the methods supported by MySQL to search for a string or substring with the help of examples and observed the output to understand.