When we write queries, we might not know the whole details about the data we are searching for. Sometimes we may require list of records ranging between some categories. If the values are of number type, then we can use arithmetic operators like =, !=, <, <=, >, or >=. But if it is a character type then these arithmetic operators will not work. We need some special operators to compare strings. We might not know whole string, probably part of the string is known or we might need to find the records which contains/ start/ends with some characters or string. This can be compared using wild cards. There are two wild cards – ‘_’ to compare single character and ‘%’ to compare zero or more characters.
Suppose we want to list all the categories whose ID starts with ‘D’. Then the query can be written using ‘%’, since we don’t know how many characters can occur after ‘D’.
SELECT * FROM CATEGORY WHERE CATEGORY_ID LIKE ‘D%’;
Consider a query to get the categories whose ID starts with ‘DBMS’. Also imagine that you know what length of category ID is. Then we can write query as
SELECT * FROM CATEGORY WHERE CATEGORY_ID LIKE 'DBMS_____';
OR
SELECT * FROM CATEGORY WHERE CATEGORY_ID LIKE 'DBMS%';
When we use ‘_’ to compare the characters, it compares only single character. Hence we have to specify it as many times as the characters are. Whereas ‘%’ compares zero or more characters in the string. Hence it is enough to specify it only once in the string.
Consider below query which searches for the publisher Id which has 45 in it and ends with any two character/digits after 45. It will strictly search for the strings ending with 2 characters after 45.
SELECT * FROM PUBLISHER WHERE PUB_ID LIKE ‘%45__’;
Above query has not listed publisher – Pearson, as it does not have any characters after 45. But the same can be retrieved by using ‘%’ like below.
Let us consider some other examples: Write a query to find the book names starting with ‘Database’
SELECT * FROM BOOKS WHERE BOOK_NAME LIKE ‘Database%’;
Write a query to find the book names ending with ‘Databases’.
SELECT * FROM BOOKS WHERE BOOK_NAME LIKE ‘%Databases’;
Write a query to find the book names containing ‘Database’.
SELECT * FROM BOOKS WHERE BOOK_NAME LIKE ‘%Database%’;
Find the books which belong to category IDs starts with DBMS_10.
SELECT b.*, bc.CATEGORY_ID FROM BOOKS b, BOOK_CATEGORY bc WHERE b.ISBN = bc.ISBN AND bc.CATEGORY_ID LIKE 'DBMS_10__';
