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 can use the demo data for the SQL tutorial. Start the Database Studio as database administrator MONA with the password RED and log on to demo database DEMODB: Logging On to a Database.

Activities

You can use Database Studio to enter and execute SQL statements. More information: Working with SQL Statements: Overview

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

Result

`FIRSTNAME`	`NAME`
`Rose`	`Brian`
`Martin`	`Randolph`

See also: SQL Reference Manual, LIKE Predicate (like_predicate)

More Information

More examples for Data Query