If not all characters are known in a column, incomplete search values can be used. It is possible to search for character strings within a column. This is not supported for numeric columns.
- The number of characters is unknown: LIKE '%abc%'
All values that contain the character string 'abc' are searched. This string can be preceded or followed by any number of characters or no character at all.
Find all customers with 'SOFT' at the end of their names:
SELECT name, city
FROM customer
WHERE name LIKE '%SOFT'
The character '*' can be used instead of '%'.
- A fixed number of characters is known: LIKE '_c_'
If the number of unknown characters is fixed and known, the positions in question can be exactly determined.
An alternative notation for '_' is '?'.
Find all customers whose names consist of six letters and begin with 'P':
SELECT name, city
FROM customer
WHERE name LIKE 'P?????'
Find all customers whose first names have any lengths and begin with 'M':
SELECT firstname, name, city
FROM customer
WHERE firstname LIKE 'M%'
Which customer names have an 'o' anywhere after the first letter?
SELECT name, city
FROM customer
WHERE name LIKE '_%o%'
If one of the special characters *, %, ?, _ is to be searched within the table rows, it must be masked using an ESCAPE character. This character can be chosen freely.
Find all customers whose names contain an '_'. In this example, the @ sign is used as the ESCAPE character.
SELECT name, city
FROM customer
WHERE name LIKE '%@_%' ESCAPE '@'