mysql   like operator

the MySQL like operator allows you to select data from a table based on a specified pattern.

the like operator in MySQL appropriate way is essential to increase the query performance.

the like operator is often used in the where clause of the select statement.

mysql  like operator syntax

                   select      column_name(s)

                   from        table_name
                           
                   where      column_name    like    pattern; 

two wildcard characters using with the like operator, the percentage % and underscore _ .

  • the percentage (%) wildcard allows you to match any string of zero or more characters
  • the underscore (_) wildcard allows you to match any single character

demo database table

below is a selection from the "user" table:

useridfirstnamelastnameemailcity
1sandeepkumarnehrasandeep76@gmail.comindia
2shyamdhakashyamkumar12@gmail.comjaipur
3ankitnehraankit11@gmail.comuae

mysql like operatro example

the following mysql statement selects all users with a firstname starting with the letter "s"

       
                   select   *        from     user

                   where       firstname     like   's%' ;

output :

useridfirstnamelastnameemailcity
1sandeepkumarnehrasandeep76@gmail.comindia
2shyambondkumarshyam@gmail.comjaipur

the following mysql statement selects all users with a firstname ending with the letter "t"

       
                   select   *        from     user

                   where       firstname     like   '%t' ;

output :

useridfirstnamelastnameemailcity
3ankitnehraankit11@gmail.comuae

the following mysql statement selects all users with a email containing the pattern '.com' .

       
                   select   *        from     user

                   where       email     like   '%.com%' ;

output : try it yourself

like with underscore (_) wildcard

find all user whose city starts with u, ends with e and contains any single character between e.g. uae,upe.

       
                   select   *        from     user

                   where       city     like   'u_e' ;

output :

useridfirstnamelastnameemailcity
3ankitnehraankit11@gmail.comuae

like with not operator

you can combine the not operator with the like operator to find string that does not match a specific pattern.

the following mysql selects all users with city not containing the pattern 'u_e'.

       
                   select   *        from     user

                   where       city     not like   'u_e' ;

output :

useridfirstnamelastnameemailcity
1sandeepkumarnehrasandeep76@gmail.comindia
2shyambondkumarshyam@gmail.comjaipur

note :   the pattern is not case sensitive with the like operator.the 's%' and 's%' pattern produce the same result.