Now that's pretty cool. I wasn't familiar with locate.
I'll have to look that up.
Thanks
Mike
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of sjl
Sent: Wednesday, December 28, 2011 4:12 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: finding a value and a subsequent value
Not pretty, but this will work:
select COMNT from MYFILE
where upper(COMNT) like '%SIZE%'
and substr(COMNT,locate('SIZE',upper(COMNT),1)+5,1)
in ('1','2','3','4','5','6','7','8','9','0')
Regards,
Steve
"sjl" wrote in message
news:mailman.970.1325105659.2619.midrange-l@xxxxxxxxxxxx...
Mike -
Specify the trailing spaces in your LIKE clause.
However, I might do it this way to catch anything entered in the comments
field in mixed case:
Select COMNT from MYFILE where UPPER(COMNT) like('%SIZE %')
Regards,
Steve
Mike wrote:
I am trying to see if I can do this with sql.
I am searching through comments in a file
I have records like this
'Jane measured size 12'
'Sue called with her size'
So for example purposes lets say I have field ACCT, and COMNT
I want to find records where the word 'size' is in the comment' easy
enough,
Select COMNT from MYFILE where COMNT like('%size'%)
But I only want records where there is a number 2 spaces after the word
'size'
Is it possible to do this with an sql statement?
--
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.
NOTICE: This message, including any attachment, is intended as a confidential and privileged communication. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender and delete this message.
midrange.com
