I recently had a case where I needed to stack the strings that I did want, minus those that I didn't. The in() and like() options weren't all that flexible given their assumption of known strings and I needed to accommodate near-hits. I found that group_concat() and regexp() work pretty well together, so that you can have offsetting populations:
First, arrange two views, one with the strings you're seeking, then another with those you're skipping. The subquery here uses a reference table (tbl_misc) and so it's optional, but allows for an external maintenance point...in case this is a persistent situation. One caveat though: regexp() is intentionally limited in the general release, and a little flaky when it comes to collations. It does handle ^string vs string$, but only if they're placed accordingly at the head or back of the line... SELECT DISTINCT -- Select only column_name values that match [SEEK|SKIP] list in tbl_misc... column_name FROM vw_OD_distinct_columnnames WHERE column_name -- Works as hard-coded list... -- REGEXP ('_BEG_|addr|init|loan|merch|name|score|ssn|_END_') -- Initially failed to match as a flattened resultset... -- Resolved by appending a dummy string...and matching suffix (_BEG_ .. _END_) REGEXP ( SELECT "'_BEG_|" || group_concat(code_val,"|") || "|_END_'" regex_arr FROM ( SELECT code_val FROM tbl_misc WHERE code_key == 'SEEK' -- alternatively 'SKIP' AND attrib IS NULL GROUP BY 1 ) ) AND ( -- Only select columns that are well-formed, ie "_" or alphabetical start... unicode(lower(column_name)) == 95 OR unicode(lower(column_name)) between 97 and 122 ) ORDER BY -- Eliminate case factors in sorting... CASE WHEN unicode(lower(column_name)) < 95 THEN unicode(lower(column_name)) + 32 ELSE unicode(lower(column_name)) END ,1 Then a subsequent view joins the two together, using EXCEPT: SELECT DISTINCT -- Streamlined list of column_names, based on _seek and _skip... column_name FROM ( -- Outer query required to offset quirk that prevented ORDER BY below... SELECT * FROM vw_sub_strings_seek EXCEPT SELECT * FROM vw_sub_strings_skip ) ORDER BY CASE WHEN unicode(lower(column_name)) < 95 THEN unicode(lower(column_name)) + 300 ELSE unicode(lower(column_name)) END Regards. Brian P Curley On Wed, Apr 19, 2017 at 4:47 PM, R Smith <rsm...@rsweb.co.za> wrote: > > On 2017/04/19 9:12 PM, Stephen Chrzanowski wrote: > >> I'm attempting to get a report given by TrustWave trimmed down to results >> that can be more easily managed. I've taken the results of a report, >> cleaned it up with Excel, then used SQLite Expert to import that result >> into a database. >> >> Here are the two table DDLs: >> >> CREATE TABLE [SkipRemed] ( >> [Skip] CHAR); >> >> >> CREATE TABLE [TWScan] ( >> [ExtIP] CHAR, [IntIP] CHAR, [Service] CHAR, [VulnName] CHAR, >> [Desc] CHAR, >> [Remediation] CHAR, [Port] CHAR, [Severity] CHAR, [CVE] CHAR, >> [Ticket] INTEGER); >> >> There's only 2049 results in TWScan, so I'm not concerned about speed, and >> there is no direct relationship between the two tables. That is the >> entire >> DDL for the entire database. >> >> What I want to do is use SkipRemed to "Filter Out" or "Filter For" results >> in TWScan based on the Remediation table. The difference between Out and >> For would be just in the calling query. Right now, I want to filter OUT >> results in SkipRemed so I can more easily see what reports are commonly >> themed and result in one action being required to correct. (IE: Upgrade >> PHP or Apache). >> >> The query I've been messing with is this: >> >> *select distinct ExtIP, IntIP, Service,Remediation from PMEScan where >> Remediation not like (select distinct Skip from SkipRemed) order by >> upper(Desc),upper(Service)* >> >> I don't get the results I want, unless I use the actual full text of the >> Remediation text. I've changed Skip to '%'||Skip||'%' in the subquery but >> that doesn't get me the results I want either. >> >> Somethings up with my logic, not sure where. Ideas? >> > > SELECT DISTINCT ExtIP, IntIP, Service, Remediation > FROM PMEScan > WHERE NOT EXISTS(SELECT 1 FROM SkipRemed WHERE Remediation LIKE Skip) > AND ... > ORDER BY ... > > > Should do it. > Cheers, > Ryan > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users