LIKE clauses are used in the WHERE conditions to compare the characters in the string. This clause helps in checking the partial string characters with another string.
Suppose we need to find the publishers whose ID has stating digits 45. Ideally publisher ID is of the format XXXX_0000. Above request is to find the publishers with ID XXXX_45NN format. This can be searched using LIKE clause as below.
SELECT * FROM PUBLISHER WHERE PUB_ID LIKE ‘%45__’;
This query uses LIKE clause with ‘%’ to compare any number of characters at the beginning and two characters after 45 at the end. It searches all the strings ending with 45 and 2 more characters. When we use ‘_’ as a search character, it compulsorily searches for the one character per underscore. If we need to search zero or more characters after 45, then we have to specify ‘%’ instead of ‘_’ in above query.
SELECT * FROM PUBLISHER WHERE PUB_ID LIKE ‘%45%’;
If we need to specifically search 45 at 6th and 7th position of the string, then we cannot use ‘%’ while comparing since it searches for zero or more characters. We have to use ‘_’ as shown below :
SELECT * FROM PUBLISHER WHERE PUB_ID LIKE ‘_____45%’;
Above examples clearly illustrates when to use ‘%’ and ‘_’. Let us consider another example for using LIKE clause.
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__';