I am using DB2 UDB V8.1 with FixPack 5. I have a table test1 with one column col1 and I have the following data in the table:

COL1
----------
C_TEST1
COMMON
C_TEST2
COX CABLE
C__TEST1
C__OMMON
C__TEST2
COX_CABLE
R__TEST1
R__OMMON
R__TEST2
ROX_CABLE

12 record(s) selected.

I want to select all the records which are like C_ and R_.

I am issuing the following command to get the records which are like C_ and R_

1) db2 "select * from test1 where col1 like 'C\_%' or col1 like 'R\_%' ESCAPE '\'"

COL1
----------
R__TEST1
R__OMMON
R__TEST2

3 record(s) selected.

When I issue the command

2) db2 "select * from test1 where col1 like 'R\_%' or col1 like 'C\_%' ESCAPE '\'"

I am getting the following result:

COL1
----------
C_TEST1
C_TEST2
C__TEST1
C__OMMON
C__TEST2

5 record(s) selected.

I am expecting my result set to be UNION of 1) and 2).

Can anybody tell me if I am doing anything wrong or there is a problem with UDB?

ESCAPE '\' must be a part of each LIKE predicate:

You can also try

SELECT * FROM TEST1 WHERE SUBSTR(COL1,1,2) = 'C_' OR SUBSTR(COL1,1,2) = 'R_'