× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



On 19-May-2011 08:29 , CRPence wrote:
execsql "drop table qtemp/userlist"
execsql "drop table qtemp/userslt"
execsql "create table qtemp/userlist (u varchar(10) allocate(8))"
execsql "create table qtemp/userslt (s varchar(10) allocate(8))"
execsql "insert into qtemp/userlist values",
" ('BOB'),('CHARLIE'),('DAVID'),('JACK'),('JOHN'),('MICHAEL')",
",('RICHARD'),('SAMUEL'),('SARAH'),('TOM'),('TOMMY'),('TORI')"
execsql "insert into qtemp/userslt values",
" ('BOB'),('DAVE'),('J*'),('TOM*')"

Using the same setup as in quoted text above and the same assumption about both that the first asterisk indicating a generic prefix search value precedes and that there is a limit of ten bytes for the queried column and search values, the following query can produce the results directly without any need to generate the query dynamically as was done in the REXX of the prior reply:

with sltctl (len, cmp) as /* CTE of control file; limited slt */
( select int(min(nullif(locate('*',s),0)-1,length(s)))
, s /* replace(s,'*','') is prettier, but who's looking */
from qtemp/userslt )
select * from qtemp/userlist
where u in (select cmp from sltctl where len is null)
or left(u,1) in (select left(cmp,len) from sltctl where len=1)
or left(u,2) in (select left(cmp,len) from sltctl where len=2)
or left(u,3) in (select left(cmp,len) from sltctl where len=3)
or left(u,4) in (select left(cmp,len) from sltctl where len=4)
or left(u,5) in (select left(cmp,len) from sltctl where len=5)
or left(u,6) in (select left(cmp,len) from sltctl where len=6)
or left(u,7) in (select left(cmp,len) from sltctl where len=7)
or left(u,8) in (select left(cmp,len) from sltctl where len=8)
or left(u,9) in (select left(cmp,len) from sltctl where len=9)

For reference, the CTE result set with the setup data looks like:

LEN CMP
- BOB
- DAVE
1 J
3 TOM
*** End of data ***

Regards, Chuck

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.