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