SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples


Written by - Falguni Thakker
Reviewed by - Deepak Prasad

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

Wildcard character Description
% 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.
Student Table

student_id studentname admissionno admissiondate enrollmentno date_of_birth email city class_id
101 reema 10001 02-02-2000 e15200002 02-02-1990 reema@gmail.com surat 2
102 kriya 10002 04-05-2001 e16200003 04-08-1991 kriya@gmail.com surat 1
103 meena 10003 06-05-1999 e15200004 02-09-1989 meena@gmail.com vadodara 3
104 carlin 2001 04-01-1998 e14200001 04-04-1989 carli@gmail.com vapi 1
105 dhiren 2002 02-02-1997 e13400002 02-02-1987 dhiru@gmail.com vapi 2
106 hiren 2003 01-01-1997 e13400001 03-03-1887 hiren@gmail.com surat 2
107 mahir 10004 06-09-2000 e15200003 07-09-1990 mahi@gmail.com vapi 3
108 nishi 2004 02-04-2001 e16200001 03-02-1991 nishi@gmail.com vadodara 1

Result Table

result_id student_id examname examdate subject obtainmark totalmarks percentage grade status
3001 101 sem1 07-08-2001 1 80 100 80 A+ pass
3002 101 sem1 08-08-2001 2 76 100 76 A+ pass
3003 102 sem3 05-05-2000 3 67 100 67 A pass
3004 102 sem3 06-05-2000 4 89 100 89 A+ pass
3005 102 sem3 07-05-2000 5 90 100 90 A+ pass
3006 103 sem5 08-09-1998 6 55 100 55 B pass
3007 103 sem5 09-09-1998 7 30 100 30 D fail
3008 103 sem5 10-09-1998 8 34 100 34 D fail

 

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.

OUTPUT:

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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.

OUTPUT:

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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.

OUTPUT:

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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');

OUTPUT:

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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');

OUTPUT:

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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[13]')

OUTPUT:

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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]%');

OUTPUT:

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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]%')

OUTPUT:

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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

OUTPUT:

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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;

OUTPUT:

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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.

OUTPUT:

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 LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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’

OUTPUT:

To see the result of this modification, we must have to use SQL SELECT command

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

 

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');

OUTPUT:

SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

When we execute above query first it checks value of student name not ended by character ‘n’.

 

Summary

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.

 

References

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15

 

Further Reading

https://docs.microsoft.com/en-us/previous-versions/troubleshoot/visualstudio/foxpro/use-wildcard-characters-sql-statement

 

Falguni Thakker

She is a dedicated professional with expertise in SQL, Python, C++, and Linux. Currently serving as a professor at a prestigious university. With a passion for teaching and a strong technical background, she inspires the next generation of computer scientists. You can connect with her on LinkedIn.

Categories SQL

Can't find what you're searching for? Let us assist you.

Enter your query below, and we'll provide instant results tailored to your needs.

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can send mail to admin@golinuxcloud.com

Thank You for your support!!

Leave a Comment

X