Like Clause:





The SQL like clause uses wild card operators to compare some values to other similar values. The LIKE operator is mostly used in WHERE clause to find the matching content from a column in the table. There are two wild cards that are used with the LIKE operator.




The wild cards used with like operator are as given below:

A percentage sign (%)

An underscore (_)

The percentage sign is used to represent a zero, or one or multiple characters. The underscore is used to represent a single character or a single number. You can also use these characters in combination.

SYNTAX

SELECT column_name

FROM table_name

WHERE column_name LIKE pattern;

In the above lines, the pattern will be consisting of the wild cards.

EXAMPLE

Consider the following table named STUDENT:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

Let’s suppose that the user wants to display the data of the students whose name start from the letter J. Then the statement will be:

SELECT *FROM student WHERE name like J%;

This query will display or return all the records in which the names are started from the letter J. The result generated by this query is as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96




EXAMPLE when both percentage and underscore are used in combination:

  1. Suppose you want to display the records of the students whose names contain o as the second character in their name then you will use the following query:

SELECT * FROM student WHERE name like ‘_o%’

Then the result generated by this query will be as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
150 Louis Marketing 1000 SPSS 19/06/97
  1. Suppose you want to display the records of the students whose names contain s as the last character in their name then you will use the following query:

SELECT * FROM student WHERE name like ‘%x’

Then the result generated by this query will be as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
140 Jones Accounting 1200 TAXACCT 12/08/97
150 Louis Marketing 1000 SPSS 19/06/97
110 James Info. System 1100 COBOL 22/07/96

What happens if write the WHERE clause with like operator using the wild cards in the following patterns:

Query Description
WHERE name LIKE ‘a%’ It returns those names that are starting with the letter a.
WHERE name LIKE ‘%a%’ It will return those names that are having the letter a in any position in the name.
WHERE name LIKE ‘_as%’ This will return those names that have as in the second and third positions respectively.
WHERE name LIKE ‘a_a_% This will return those names that are starting with “a” and has a length of at least 3 characters.
WHERE name LIKE ‘%a’ This will return the names that are ending with the letter a.
WHERE name LIKE ‘_a%b’ This will return the names that have the letter a in the second position of the name and the name is ending with the letter b.
WHERE name LIKE ‘a__b’ This will return the names that are 5 characters long and are starting with the letter a and ending with the letter b.