Page 1 of 1

Problem with LIKE clause

PostPosted: Fri Dec 17, 2010 1:21 pm
by sriraj1122
Hi All,

I am facing some problem with LIKE clause in DB2 query

I have rows as below for column name PROGRAM_NAME

PROGRAM_NAME
*******************
AA12BB
AA11BB
AA13BB
AA11CC
AA12CC
ABCDEF

I need to the result which are starting with AA and ending with BB as below


PROGRAM_NAME
*******************
AA12BB
AA11BB
AA13BB

I have used the below query

SELECT * FROM TERROR_LOG
WHERE PROGRAM_NAME LIKE 'AA__BB';;

But im getting zero rows.

Could you please help in this regard.

Re: Problem with LIKE clause

PostPosted: Fri Dec 17, 2010 6:35 pm
by GuyC
Is program_name varchar or a char(8)?
Then probably there are spaces after 'BB' , try like 'AA__BB%' or 'AA__BB ' or ...

Re: Problem with LIKE clause

PostPosted: Fri Dec 17, 2010 6:42 pm
by alexm
Hi,
assuming you're working with DB2 z/OS: see "IBM DB2 SQL Reference, Chapter 2 Language elements, Predicates, LIKE predicate" for a description of LIKE patterns and rules. Sample queries 2 and 3 below do what you're looking for (depending on datatype of column PROGRAM_NAME).
Best regards, Alex

CREATE TABLE TERROR_LOG (PROGRAM_NAME CHAR(8));           
INSERT INTO  TERROR_LOG VALUES('AA12BB');                 
INSERT INTO  TERROR_LOG VALUES('AA11BB');                 
INSERT INTO  TERROR_LOG VALUES('AA13BB');                 
INSERT INTO  TERROR_LOG VALUES('AA11CC');                 
INSERT INTO  TERROR_LOG VALUES('AA12CC');                 
INSERT INTO  TERROR_LOG VALUES('ABCDEF');                 
                                                           
SELECT PROGRAM_NAME                                       
FROM   TERROR_LOG                                         
WHERE  PROGRAM_NAME LIKE 'AA__BB' ;                       
-----------------------------------------------------------
PROGRAM_NAME                                               
-----------------------------------------------------------
DSNE610I NUMBER OF ROWS DISPLAYED IS 0                     
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
                                                           
SELECT PROGRAM_NAME                                       
FROM   TERROR_LOG                                         
WHERE  PROGRAM_NAME LIKE 'AA__BB%' ;                       
-----------------------------------------------------------
PROGRAM_NAME                                               
-----------------------------------------------------------
AA12BB                                                     
AA11BB                                                     
AA13BB                                                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 3                     
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
                                                           
SELECT PROGRAM_NAME                                       
FROM   TERROR_LOG                                         
WHERE  PROGRAM_NAME LIKE 'AA__BB  ' ;                     
-----------------------------------------------------------
PROGRAM_NAME                                               
-----------------------------------------------------------
AA12BB                                                     
AA11BB                                                     
AA13BB                                                     
DSNE610I NUMBER OF ROWS DISPLAYED IS 3                     
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
                                                           
DROP TABLE TERROR_LOG;                                     

Re: Problem with LIKE clause

PostPosted: Tue Dec 21, 2010 11:29 am
by sriraj1122
Thnq all

Re: Problem with LIKE clause

PostPosted: Sun Jan 16, 2011 10:10 am
by ankesh.cs2007
The problem is that you are using AA_BB. The character _ is used only for single character but as you specified that your table contains name like AA01BB. Try this code select * from terror_log where program_name like trim(AA%BB);
This is because trim removes all the blank spaces present on both the sides of string.