TSO-ISPF JCL COBOL VSAM DB2 CICS Tools Articles Job Portal Forum Quiz Interview Q&A

DB2 - SQL Like Operator


Wildcards are special characters used to match parts of a value in the where clause.

To use wildcards in search clauses, the LIKE operator must be used.

Wildcard searching can be used only with text fields (strings).


There are two wildcards often used in conjunction with the LIKE operator:

  1. % - Means match any number of occurrences of any character.

  2. _ - The underscore represents a single character

Syntax:

SELECT column1, column2, ...
  FROM table_name
  WHERE columnN LIKE pattern;

You can also combine any number of conditions using AND or OR operators.


Here are some examples showing different LIKE operators with '%' and '_' wildcards.

LIKE OperatorDescription
WHERE StudentName LIKE 'b%'Finds any values that start with "b"
WHERE StudentName LIKE '%ed%'Finds any values that have "ed" in any position
WHERE StudentName LIKE 'a_%'Finds any values that start with "a" and are at least 2 characters in length
WHERE StudentName LIKE 'b%t'Finds any values that start with "b" and ends with "t"
WHERE StudentName LIKE '%t'Finds any values that end with "t"
WHERE StudentName LIKE '_a%'Finds any values that have "a" in the second position
WHERE StudentName LIKE 's__%'Finds any values that start with "s" and are at least 3 characters in length

DB2 Database:

Below is a selection from the "Product" table in the DB2 database.

ProductIDProductNameCategoryPriceQtyPriceTotalValue
7001MouseAccessories75.00
7002Harddrive65.00201,300
7003KeyboardAccessories36.00331,118.00
7004RAMComponents23.5016376.00
7005HondaBikes1,200
7006PEN7.451074.50
7007CddriveAccessories75.00
7008SpeakerAccessories75.00


Example 1:

The following SQL statement selects all Product with a ProductName starting with "H":

SELECT * FROM Product
    WHERE ProductName LIKE 'H%';
Result:
ProductIDProductNameCategoryPriceQtyPriceTotalValue
7002Harddrive65.00201,300
7005HondaBikes1,200


Example 2:

The following SQL statement selects all Product with a ProductName ending with "e":

SELECT * FROM Product
    WHERE ProductName LIKE '%e';
Result:
ProductIDProductNameCategoryPriceQtyPriceTotalValue
7001MouseAccessories75.00
7002Harddrive65.00201,300
7007CddriveAccessories75.00


Example 3:

The following SQL statement selects all Product with a ProductName that have "dd" in any position:

SELECT * FROM Product
    WHERE ProductName LIKE '%dd%';
Result:
ProductIDProductNameCategoryPriceQtyPriceTotalValue
7002Harddrive65.00201,300
7007CddriveAccessories75.00


Example 4:

The following SQL statement selects all Product with a ProductName that have "o" in the second position:

SELECT * FROM Product
    WHERE ProductName LIKE '_o%';
Result:
ProductIDProductNameCategoryPriceQtyPriceTotalValue
7001MouseAccessories75.00
7005HondaBikes1,200


Example 5:

The following SQL statement selects all Product with a ProductName that starts with "H" and ends with "e":

SELECT * FROM Product
    WHERE ProductName LIKE 'H%e';;
Result:
ProductIDProductNameCategoryPriceQtyPriceTotalValue
7002Harddrive65.00201,300


Example 6:

The following SQL statement selects all Product with a ProductName that does NOT start with "H":

SELECT * FROM Product
    WHERE ProductName NOT LIKE 'H%';
Result:
ProductIDProductNameCategoryPriceQtyPriceTotalValue
7001MouseAccessories75.00
7003KeyboardAccessories36.00331,118.00
7004RAMComponents23.5016376.00
7006PEN7.451074.50
7007CddriveAccessories75.00
7008SpeakerAccessories75.00


If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!

Are you looking for Job Change? Job Portal