Problem with LIKE clause



IBM's flagship relational database management system

Problem with LIKE clause

Postby sriraj1122 » Fri Dec 17, 2010 1:21 pm

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.
sriraj1122
 
Posts: 19
Joined: Thu Nov 18, 2010 10:04 am
Has thanked: 0 time
Been thanked: 0 time

Re: Problem with LIKE clause

Postby GuyC » Fri Dec 17, 2010 6:35 pm

Is program_name varchar or a char(8)?
Then probably there are spaces after 'BB' , try like 'AA__BB%' or 'AA__BB ' or ...
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Problem with LIKE clause

Postby alexm » Fri Dec 17, 2010 6:42 pm

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;                                     
User avatar
alexm
 
Posts: 35
Joined: Wed Oct 13, 2010 6:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Problem with LIKE clause

Postby sriraj1122 » Tue Dec 21, 2010 11:29 am

Thnq all
sriraj1122
 
Posts: 19
Joined: Thu Nov 18, 2010 10:04 am
Has thanked: 0 time
Been thanked: 0 time

Re: Problem with LIKE clause

Postby ankesh.cs2007 » Sun Jan 16, 2011 10:10 am

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.
ankesh.cs2007
 
Posts: 4
Joined: Wed Jan 12, 2011 1:46 am
Location: meerut, india
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post