SQL LIKE | SQL NOT LIKE | SQL LIKE WILDCARD Examples

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

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

Result Table

 

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:

 

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 5^th.

OUTPUT:

 

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 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:

 

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 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:

 

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 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 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 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 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 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 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:

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