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