Searching for Character Strings: LIKE 

To select specific rows, you can use the WHERE clause. If not all the characters in a column value are known, you can also specify incomplete search values. The LIKE predicate is provided for this purpose.

Prerequisites

You require the demo data for the SQL Tutorial.

Start the query tool SQL Studio as database administrator MONA with password RED and log on to the demo database instance DEMODB.

Examples

SELECT firstname, name
  FROM hotel.customer
    WHERE name LIKE 'P%'

Selecting the customers whose last names begin with P

Result

FIRSTNAME	NAME
Jenny	Porter
Joseph	Peters

 

SELECT firstname, name
  FROM hotel.customer
    WHERE name LIKE '%er'

Selecting the customers whose last names end in er 

Result

FIRSTNAME	NAME
Jenny	Porter
Frank	Miller
Susan	Baker

 

SELECT firstname, name
  FROM hotel.customer
    WHERE firstname LIKE '_i__'

Selecting the customers whose first names are four characters long and that contain i as the second letter

Result

FIRSTNAME	NAME
Mike	Jackson
Rita	Doe

 

SELECT firstname, name
  FROM hotel.customer
    WHERE name LIKE '_%an%'

Selecting the customers whose last names contain an after the first position

Result

FIRSTNAME	NAME
Rose	Brian
Martin	Randolph

 

See also:

SQL Reference Manual, LIKE Predicate (like_predicate)

More examples for Data Query