There may be a situation where you wanna fetch records from a table that presents a desirable character or word of your choice in a field, suppose you need all those records in which addresses belong to a particular City, which cannot be grabbed by using assignment operator (‘=’) because an address includes street name,locality, pin code and then City name. To get the address of a specific city name, we can pull it from table by ‘LIKE’ keyword.
For Example:-
| NAME | ADDRESS |
| xyz | 1-A,satluj Road,Janakpuri, Delhi |
You cannot fetch it by = operator:
SELECT * FROM TableName WHERE address =’DELHI’;
For this LIKE operator is used.
LIKE keyword is used to find out all those rows that satisfy a matching patterns in a field.
In Other word, LIKE keyword’s job is to find out a given pattern in a particular field.
However, it doesn’t do the job all alone, a wildcard comes up with it.
A wildcard provides flexibility to find out any word or set of words from a field in a table.
1) % (percentage sign) :– It is used in place of unknown sequence of characters .
2) _ (underscore sign):– It is used in place of only a single not known character.
Both can be used at start, middle and end of the given sequence of characters.
| Patterns | Length | Use |
|---|---|---|
| ‘xyz%’ | Can be of anything | It gets all those sentences or words that start with ‘xyz’ |
| ‘%xyz’ | Can be of anything | It gets all those sentences or words that end up with ‘xyz’ |
| ‘%xyz%’ | Can be of anything | It finds all the sentences or words in which ‘xyz’ in between |
| “x%z” | 3 (2 character + 1 Underscore) | It finds all the sentences or words in whose first character would be ‘x’ and last character would be z, and anything can have in between these |
| ‘_yz’ | 3 (2 character + 1 Underscore) | It fetches all the sequence of characters whose first character can be anything and ends with yz |
| ‘xy_’ | 3 (2 character + 1 Underscore) | It fetches all the sequence of characters whose last character can be anything and starts with xy |
| ‘x_z’ | 3 (2 character + 1 Underscore) | It fetches all the sequence of characters whose middle character can be anything that is in between x and y |
Note: Underscore stands for only a single character while percentage can be any number of characters
Syntax:
SELECT columns,columns1… FROM tableName WHERE Column LIKE ‘Pattern’;
Suppose I have the following table:-
1. Find all those records whose addresses which belong to DELHI
Query: SELECT * FROM employees WHERE address LIKE ‘%delhi’;
2. Find all those records whose addresses consists of XYZ
Query: SELECT * FROM employees WHERE address LIKE ‘xyz%’;
3. Find all records whose name’s first character can be anything but ends with ‘ohn’
Query: SELECT * FROM employees WHERE address LIKE ‘_ohn’;
4. Find all records whose name’s last character can be anything but starts with ‘joh’
Query: SELECT * FROM employees WHERE address LIKE ‘joh_’;
This post was last modified on July 4, 2022