Thread: DB2 9.7 LIKE operator issue
07-01-11, 08:54 #1Registered User
- Join Date
- Jul 2011
Unanswered: DB2 9.7 LIKE operator issue
I have a table T:
create table T(C1 VARCHAR(64));
and some records in it:
and create index on c1.
I want records which begin with '010'.
Now, two query:
(1) select * from t where c1 like '010%';
(2) select * from t where c1>='010' and c1 < '011';
have the same result:
My question is:
(1) How does DB2 compare two strings? The comparison is related to codepage?
(2) Is query (2) a optimized way of query (1)? If not, can someone give a record which appears in the result of query (2) but not begins with '010'?
(3) How does query (2) utilize index?
I am from China. Thank you for your reply.
07-01-11, 11:35 #2Registered User
- Join Date
- Jan 2007
- Jena, Germany
(1) It depends on your version and platform. DB2 z/OS has does a simply binary comparison and code page conversions occur during bind-in/bind-out of values. For DB2 LUW, I am not sure - I'd expect the same to happen but maybe different collations come into play.
(2) Yes, both queries should be semantically identical.
(3) Query (2) will exploit an index in the usual way: it does a range-search finding the lower/upper keys and scans the index leaf pages for all matching index entries.Knut Stolze
IBM DB2 Analytics Accelerator
IBM Germany Research & Development