 |
| LIKE predicate in Search condition in DB2 for i SQL |
It is used to search for a specified pattern in a column and is used in WHERE clause. There are two wildcards used with LIKE operator.
Start With (% wildcard)
For Example, refer to the Customer table and find out all the customer whose name starts with letter 'A'.
SELECT * FROM customer WHERE CUSTNAME LIKE 'A%'
Output:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
1 Amit PATNA BIHAR INDIA
2 Anil PATNA BIHAR INDIA
8 Annamalai CHENNAI TAMIL NADU INDIA
Underscore (_) wildcard
Next Example, refer to the Customer table and find out all customers whose name starts with 'A' followed by one wildcard character, then 'i' and then one wildcard character.
SELECT * FROM customer WHERE CUSTNAME like 'A_i_'
Output:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
1 Amit PATNA BIHAR INDIA
2 Anil PATNA BIHAR INDIA
Contains
Next Example, refer to the Customer table and find out all customers from a city that contains the letter 'N':.
SELECT * FROM customer WHERE CUSTCITY LIKE '%N%'
Output:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
1 Amit PATNA BIHAR INDIA
2 Anil PATNA BIHAR INDIA
8 Annamalai CHENNAI TAMIL NADU INDIA
9 Kannan CHENNAI TAMIL NADU INDIA
Start With(OR operator)
Next Example, refer to the Customer table and find out all customers whose name starts with 'A' or starts with 'U'.
SELECT * FROM customer WHERE CUSTNAME LIKE 'A%' OR CUSTNAME LIKE 'U%'
Output:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
1 Amit PATNA BIHAR INDIA
2 Anil PATNA BIHAR INDIA
4 Udeep KOCHI KERELA INDIA
8 Annamalai CHENNAI TAMIL NADU INDIA
End With
Next Example, refer to the Customer table and find out all customers whose name ends with 'n'.
SELECT * FROM customer WHERE CUSTNAME LIKE '%n'
Output:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
5 Yatin IDUKKI KERALA INDIA
9 Kannan CHENNAI TAMIL NADU INDIA
Start With and End With
Next Example, refer to the Customer table and find out all customers whose name starts with 'A' and ends with 't'.
SELECT * FROM customer WHERE CUSTNAME LIKE 'A%t'
Output:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
1 Amit PATNA BIHAR INDIA
Combine Wildcards
Any wildcard like % and _ can be used in combination with other wildcards.
Next Example, refer to the Customer table and find out all customers whose name starts with "A" and are at least 5 characters in length.
SELECT * FROM customer WHERE CUSTNAME LIKE 'A____%'
Output:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
8 Annamalai CHENNAI TAMIL NADU INDIA
Next Example, refer to the Customer table and find out all customers whose name have "n" in the third position
SELECT * FROM customer WHERE CUSTNAME LIKE '__n%'
Output:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
6 Tinku AGRA UTTAR PRADESH INDIA
8 Annamalai CHENNAI TAMIL NADU INDIA
9 Kannan CHENNAI TAMIL NADU INDIA
Without Wildcards
If no wildcard is specified, the phrase has to have an exact match to return a result.
Next Example, refer to the Customer table and find out all customers whose City is CHENNAI.
SELECT * FROM customer WHERE CUSTCITY LIKE 'CHENNAI'
Output:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
8 Annamalai CHENNAI TAMIL NADU INDIA
9 Kannan CHENNAI TAMIL NADU INDIA
LIKE predicate escape clause
INSERT INTO EASYCLASS1/CUSTOMER (CUSTID, CUSTNAME, CUSTCITY, CUSTSTATE, CUSTCOUNTRY) VALUES(10, 'TE_ST', 'NOIDA', 'UTTARPRADESH', 'INDIA'), (11, 'T%EST', 'NOIDA', 'UTTARPRADESH', 'INDIA')
Now the file has below data:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
1 Amit PATNA BIHAR INDIA
2 Anil PATNA BIHAR INDIA
3 Dhanraj HISAR HARYANA INDIA
4 Udeep KOCHI KERELA INDIA
5 Yatin IDUKKI KERALA INDIA
6 Tinku AGRA UTTAR PRADESH INDIA
7 Mounish KOLKATA WEST BENGAL INDIA
8 Annamalai CHENNAI TAMIL NADU INDIA
9 Kannan CHENNAI TAMIL NADU INDIA
10 TE_ST NOIDA UTTARPRADESH INDIA
11 T%EST NOIDA UTTARPRADESH INDIA
If you want to search for a character string that contains either the underscore or percent character, use the ESCAPE clause to specify an escape character.
For searching wildcard character itself, we precede them with an escape character.
Syntax:
escape 'escape-character'
where, escape-character is any character that is supported by the DB2.
Next Example, refer to the Customer table and find out all customers whose name has wildcard character % or _.
SELECT * FROM customer WHERE CUSTNAME LIKE '%/_%' ESCAPE '/' or custname like '%/%%' ESCAPE '/'
Output:
CUSTID CUSTNAME CUSTCITY CUSTSTATE CUSTCOUNTRY
10 TE_ST NOIDA UTTARPRADESH INDIA
11 T%EST NOIDA UTTARPRADESH INDIA
We used LIKE '%/_%' ESCAPE '/' which means using LIKE operator and then using % at start and at end. After that using an escape-character '/' and then wildcard character _ that has to be searched and allowing LIKE operator to use _ as literal so after that used ESCAPE clause and then the same escape-character that we put before the wildcard character to be searched. The escape character can be different we can use @ and ! etc. escape-character.
We used LIKE '%/%%' ESCAPE '/' which means using LIKE operator and then using % at start and at end. After that using an escape-character '/' and then wildcard character % that has to be searched and allowing LIKE operator to use % as literal so after that used ESCAPE clause and then the same escape-character that we put before the wildcard character to be searched. The escape character can be different we can use @ and ! etc escape-character.