SQL LIKE || DB2 for i SQL

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.

The percent sign % represents zero, one, or multiple characters

Each underscore sign _ represents one, single character.

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.