IBM Mainframe Forum

by **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.

by **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 ...

by **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

Select all

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;

by **sriraj1122** » Tue Dec 21, 2010 11:29 am

Thnq all

by **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.

IBM Mainframe Forum

Problem with LIKE clause

Problem with LIKE clause

Re: Problem with LIKE clause

Re: Problem with LIKE clause

Re: Problem with LIKE clause

Re: Problem with LIKE clause