And, you can throw a trim() in there as well.
Select * from qtemp/myfile
where field100 not like '%' concat trim(field5) concat '%'
Gary Monnier
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Thursday, January 26, 2012 3:12 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL - "contains" function?
On 26-Jan-2012 14:20 , Timothy Adair wrote:
I've searched the archives and Googled it, but so far, no soap.
I'm using STRSQL to try to run a basic report, and I need some kind of
"Contains" function. In simplified form, here is what I'm trying to
do.
File has 2 fields:
FIELD5 (5 char. alpha)
FIELD100 (100 char. alpha)
I want a report of all records where the string in FIELD5 is not
contained anywhere within the string in FIELD100 (in the same record).
Sounds simple, right?
Apparently (best I can determine), there is no SQL function to use in
the Where clause. Or am I wrong?
LIKE doesn't help.
I know I can write a quick RPG program to do this, but if I (an
SQL-newbie) can learn a new technique, it will make my day.
The following script should help to show how LIKE is\can be helpful to effect the equivalent of the "contains" [*CT?] for OPNQRYF; notice that blanks\spaces are significant:
<code>
create table qtemp/myfile (field5 char(5), field100 char(100))
; -- Table MYFILE created in QTEMP.
insert into qtemp/myfile values
('into' ,'into qtemp/myfile values(''into'',...')
,('into' ,'qtemp/myfile values(''into'',...' )
,('six' ,'There should be sixty of those.' )
,('drive','We have no diskette drives.' )
,('setup','Setup the system to generate alerts' )
,('int' ,'insert into qtemp/myfile values(...' )
,('five' ,'There were five more than required.' )
; -- 7 rows inserted in MYFILE in QTEMP.
Select * from qtemp/myfile
where field100 not like '%' concat field5 concat '%'
; -- report from above SELECT:
....+....1....+....2....+....3....+....4
FIELD5 FIELD100
into qtemp/myfile values('into',...
six There should be sixty of those.
setup Setup the system to generate alerts
int insert into qtemp/myfile values(...
******** End of data ********
;
Select * from qtemp/myfile /* rtrim(field5) maybe instead? */
where field100 not like '%' concat strip(field5) concat '%'
; -- report from above SELECT:
....+....1....+....2....+....3....+....4
FIELD5 FIELD100
setup Setup the system to generate alerts
******** End of data ********
;
</code>
In the second example report, using SRTSEQ(*LANGIDSHR), no rows would be selected because 'Setup'='setup' in that shared-weight sequence; and for the first report using that Sort Sequence [or similar shared-weight], the FIELD5='setup' would not be included.
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
