Slide 14.12: The WHERE clause (cont.)

The WHERE Clause (Cont.)

The LIKE Condition
The LIKE condition is used to specify a search for a pattern in a column.

A ‘%’ sign can be used to define wildcards (missing letters in the pattern) both before and after the pattern.

 SELECT  column  FROM  table
   WHERE  column  LIKE  pattern

Using LIKE

LastName	FirstName	Address	City
Hansen	Ola	Timoteivn 10	Sandnes
Svendson	Tove	Borgvn 23	Sandnes
Pettersen	Kari	Storgt 20	Stavanger
Rasmussen		Storgt 67

The second SQL statement will return persons with first names that start with an ‘O.’
The third SQL statement will return persons with first names that end with an ‘e.’
The fourth SQL statement will return persons with first names that contain the pattern “ar.”

 SQL> select  *  from  Persons  where  City = Sandnes;
                                        *
 ERROR at line 1:
 ORA-00904: "SANDNES": invalid identifier


 SQL> select  *  from  Persons  where  FirstName  like  'O%';

 LASTNAME          FIRSTNAME          ADDRESS            CITY
 --------------    --------------     --------------     ------------
 Hansen            Ola                Timoteivn 10       Sandnes


 SQL> select  *  from  Persons  where  FirstName  like  '%e';

 LASTNAME          FIRSTNAME          ADDRESS            CITY
 --------------    --------------     --------------     ------------
 Svendson          Tove               Borgvn 23          Sandnes


 SQL> select  *  from Persons  where  FirstName  like  '%ar%';

 LASTNAME          FIRSTNAME          ADDRESS            CITY
 --------------    --------------     --------------     ------------
 Pettersen         Kari               Storgt 20          Stavanger

The following is an SQL test area from w3schools.com where the Customers table may be created by the following command:

   SQL> create table  Customers (
     2    CustomerID   varchar(16),
     3    CompanyName  varchar(32),
     4    ContactName  varchar(32),
     5    Address      varchar(64),
     6    City         varchar(32),
     7    PostalCode   varchar(16),
     8    Country      varchar(32) );

Note that the Customers table is for read only.