Table of Contents
Related Searches: sql like, sql like statement, like operator in sql, sql starts with, like query in sql, sql like or, sql like syntax, like command in sql, like function in sql, using like in sql, sql select like, how to use like in sql, like clause in sql, sql string like, mysql like query, like mysql, where like, like query, select like, like operator, sql search query, like keyword in sql, sql like and, sql query starts with, mysql select like, sql like example, sql where starts with, select like sql server, pattern matching in sql w3schools, like clause in sql server, like search
What is the Use of SQL Like Operator?
In SQL, like comparison operator is used to compare or to manipulate text with regular expressions. It determines whether a character string matches to a specific regular expression pattern or a pattern of wildcard characters. Determines whether a specific character string matches a specified pattern.
Regular expression must exactly match the characters specified in the character string. Whereas, wildcard characters can be matched with arbitrary piece of the character string. Two Using wildcard characters makes the LIKE operator more flexible.
There are mainly four wildcard characters are available
|%||Match any string of zero or more character|
|_||Match any single character at specific position|
|||Match within a specific range for comparison for single character range ([a-f]) or set ([abcdef]).|
|[^]||Check for specific character not within the specified range ([^a-f]) or set ([^abcdef]).|
The LIKE condition is used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.
SQL Like Operator Syntax
Column | match_expression [ NOT] LIKE pattern [ ESCAPE escape_character ];
match_expression: Is any valid expression of character data type used in a WHERE clause
Pattern: Is the specific string of characters to search for in match_expression, and can include the above valid wildcard characters. pattern can be a maximum of 8,000 bytes. Patterns are case sensitive, that is, uppercase characters do not match lowercase characters, or vice versa.
escape_character: It is a character put in front of a wildcard character to indicate that the wildcard is interpreted as a regular character and not as a wildcard. escape_character is a character expression that has no default and must evaluate to only one character.
Pattern Matching Using SQL LIKE with ‘%’ wildcard character
LIKE '5%'matches for the number 5 followed by any string of zero or more characters.
LIKE 'Intro%'matches for any string beginning with ‘Intro’
LIKE '%Comp%'matches for any string containing ‘Comp’ as a substring, for example ‘fundamental of computers’;
LIKE '_ _ _'matches for any string of exactly three characters
LIKE '_ _ _ %'matches for any string of at least three characters.
LIKE 'a%n'matches for any string which starts with a and end with n for example ‘admin’
LIKE 'd_%_%'matches for any string starts with d and having atleast 3-character length
SQL Like Operator Practical Examples
We are considering two tables of student result management system, table student and table result for examples.
SQL LIKE with ‘%’ wildcard character Examples
Retrieve student records who are living in city, name starts with ‘v’ using SQL Like operator
SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id FROM tblstudent WHERE (city LIKE 'V%');
When we execute above query, it first checks for the condition on city name to compare with pattern ‘V%’ means any city name which starts with v character.
To get student data who have appeared in month of MAY for exam, use SQL Like operator with ‘%’
SELECT result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status FROM tblresult WHERE (examdate LIKE '%-05-%')
When we execute above query it checks for a middle pattern matching that is month number in column exam date should be 5th.
Use SQL LIKE operator to find the details of all the students having ‘m’ as the first character in their names and ‘a’ as last character
SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id FROM tblstudent WHERE (studentname LIKE 'm%a')
In above query we used % wild character in pattern matching, when we execute query it compare Patten and fetch record where student name starts with m and end with a.
SQL LIKE with ‘_’ wildcard character Examples
To get records of students whose enrolment number must be of length 9 and ended with ‘00003’ number
SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id FROM tblstudent WHERE (enrollmentno LIKE '____00003');
Use SQL LIKE operator with ‘_’ to list all students whose city name is start with ‘su’ with length of 5, and student name of length 6 and having a as a last character
SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id FROM tblstudent WHERE (city LIKE 'su___') AND (studentname LIKE '____a');
SQL LIKE with [ ] wildcard character Examples
Square brackets [ ] checks for a character should be within the range at specific position in a string or in a column value.
To get list of students who are currently in semester 1 or semester 3 use SQL LIKE with 
SELECT result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status FROM tblresult WHERE (examname LIKE 'sem')
Use SQL Query with like operator to find list of students whose name start within the range of a through h.
SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id FROM tblstudent WHERE (studentname LIKE '[a-h]%');
SQL LIKE with ‘^’ wildcard character Examples
^ character is used to get data which is not fall into the range of character or with set of character pattern within
To fetch the student result record to find list of students how do not get percentage between 50 to 60 use SQL Like operator with [^]
SELECT result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status FROM tblresult WHERE (pecentage LIKE '[^50-60]%')
SQL LIKE with the ESCAPE clause example
ESCAPE instructs the LIKE operator to treat a character as a literal string instead of a wildcard
For example, to get list out the students whose emailId contains
_ character as a middle character.
SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id FROM tblstudent WHERE (email LIKE '%!_%' ESCAPE '!')
In above query we used ESCAPE to escape the meaning of
_ character in SQL pattern. When we execute above query it will return records of students whose email id contains
_ as one middle character
SQL LIKE OPERATOR with CASE Statement
SQL Case statement is like '
if..then' conditional statement of programming language but it is in pair of
when..then when used in SQL . We can use SQL CASE statement with LIKE operator to check for multiple condition with each When clause and gives result accordingly.
For example, if we want to list the student with their grade class who has pass exam more than 35 percentage that is if percentage is more than 70 than class will be Distinction and if percentage is between 60 to 70 than class will be First, and if percentage is below 60 than class will be second class.
SELECT st.studentname, r.examname, r.pecentage, CASE WHEN pecentage LIKE '[7-9]%' THEN 'Distinction' WHEN pecentage LIKE '6%' THEN 'First Class' WHEN pecentage LIKE '6%' THEN 'Second Class' WHEN pecentage LIKE '5%' THEN 'Pass Class' ELSE 'Fail' END AS Expr1 FROM tblstudent AS st INNER JOIN tblresult AS r ON st.student_id = r.student_id ORDER BY r.examname;
SQL LIKE OPERATOR with UPDATE & DELETE SQL Statement
Like Operator can we used with SQL UPDATE and SQL DELETE command also to modify and to delete records on conditional basis.
SQL Like with Update & DELETE Syntax:
UPDATE table_name SET column_name=newvalue WHERE column_name LIKE pattern|Regular_expression ; DELETE FROM table_name WHERE column_name LIKE pattern|Regular_expression;
SQL DELETE WITH LIKE Operator Example
Using SQL LIKE Operator to Delete record of student who have percentage less than 35
DELETE FROM tblresult WHERE (pecentage LIKE '3[0-5]');
When we execute above query it shows error message like two rows deleted from result table. Here the percentage is compared and check with pattern of first digit should be 3 and second digit can be between 0 to 5.
To see the output, we need to use SELECT SQL Command
SELECT result_id, student_id, examname, examdate, subjectid, obtainmark, totalmarks, pecentage, grade, status FROM tblresult;
SQL UPDATE WITH LIKE Operator Example
Execute SQL Query to modify city name of student set as ‘Surat’ where city name starts with ‘s’
UPDATE tblstudent SET city = 'Surat' WHERE (city LIKE 's%');
When we execute this query ‘3 rows updated’ message will appear. It checks for pattern matching city names, starts with ‘s’ and then replace it with ‘Surat’
To see the result of this modification, we must have to use SQL SELECT command
SQL NOT LIKE with the % wildcard character
There is a condition where we need to fetch records which do not match with pattern, in such situation we need to use SQL NOT LIKE Operator. SQL NOT LIKE is also used with SELECT, DELETE AND UPDATE Statements.
SQL NOT LIKE Syntax
SELECT column_name FROM table_name WHERE column_name NOT LIKE pattern|Regular_expreesion; UPDATE table_name SET column_name=value WHERE column_name NOT LIKE pattern|Regular_expreesion; DELETE FROM table_name WHERE column_name NOT LIKE pattern|Regular_expreesion;
SQL NOT LIKE Operator Example
Retrieve the records of student where student name does not end with ‘n’
SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id FROM tblstudent WHERE (studentname NOT LIKE '%n');
When we execute above query first it checks value of student name not ended by character ‘n’.
In this article of SQL Like Operator we have learned about use of comparison operator Like what is pattern and expression as well as we have discussed about wildcard operators and use of ESCAPE character in comparison. Searching method using LIKE Operator is to compare string value of column with specified pattern. We have also covered SQL LIKE operator used with SQL DELETE and SQL UPDATE statement with example.