Like statement using wildcards to exclude data

I'm doing a clean up  returned data is SQL by using exclusion  params in the SQL to avoid that data which is not needed.

Query underneath. :
In the "where" clause you have conditions on table data coming from "RIPD" and conditions valid for data coming from "TZD"
The conditions on RIPD seem to work and data is indeed ommitted as requested ( wildcard % only used for arguments behind the fixed characters

But on "TZD", there are i need to work with "Substrings within a set of characters" and there  the data is not ommited. I've tried  escape characters as * / % / %\ ,

But no progress, or does the error lie elsewhere ? can anybody help ?

Thanks Chriβt'll S.


  SELECT RIPD.DES "Rating Package",
         RIPVS.VSCODE "Version nr",
         ZPD.DES "Zone package",
         TZD.DES "Tariff Zone",
         TPD.DES "Time package",
         TTD.DES "Tariff Time",
         ZO.CGI "Zone Origin",
         ZD.DIGITS "Zone Destination",
         RPPV.PARAMETER_ROWNUM "Linenr",
         RPPV.PARAMETER_SEQNUM "Parameter id",
         PARAMETER_VALUE_FLOAT / 100000000 "Value (TVA exclu)"
    FROM MPULKRIM RIP,
         UDC_RATE_TYPE_TABLE RT,
         MPURITAB RIPD,
         MPUGVTAB ZPD,
         MPUZNTAB TZD,
         MPUTWTAB TPD,
         MPUTTTAB TTD,
         MPULKGVM ZP,
         MPUZPTAB ZD,
         RATE_PACK_ELEMENT RPEW,
         RATE_PACK_PARAMETER_VALUE RPPV,
         MPUZOTAB ZO,
         MPURIVSD RIPVS
   WHERE        RIPD.DES LIKE 'B-Fleet VPN Telephony%'
            AND RIPD.DES LIKE 'Telephony%'
            AND RIPD.DES NOT LIKE 'Telephony RTC%'
            AND RIPD.DES NOT LIKE 'Telephony ROC%'
            AND RIPD.DES NOT LIKE 'Telephony Add-on%'
            AND RIPD.DES NOT LIKE 'ZZ notUSED%'
            AND RIPD.DES NOT LIKE 'Add-on%'
            AND RIPD.DES NOT LIKE 'MMS Premium Originated%'
            AND RIPD.DES NOT LIKE 'SMS Premium Originated%'
            AND RIPD.DES NOT LIKE 'MMS Premium Terminated%'
            AND TZD.DES NOT LIKE '%\BASE Services%'
            AND TZD.DES NOT LIKE '%\BASE01-Test%'
            AND TZD.DES NOT LIKE '%\BASE02-Test%'
            AND TZD.DES NOT LIKE '%\BASE03-Test%'
            AND TZD.DES NOT LIKE '%\BASE010-Test%'
            AND TZD.DES NOT LIKE '%\Premium Rate Calls%'
            AND TZD.DES NOT LIKE '%\COYOTE%'
            AND TZD.DES NOT LIKE '%\SMS PO%'
            AND TZD.DES NOT LIKE '%\SMS PT%'
            AND TZD.DES NOT LIKE '%\MMS PO%'
            AND TZD.DES NOT LIKE '%\MMS PT%'
            AND TZD.DES NOT LIKE '%\Imode Rate%'
            AND TZD.DES NOT LIKE '%\BlackBerry%'
            AND TZD.DES NOT LIKE '%\Intranet%'
            AND TZD.DES NOT LIKE '%\internet over GPRS%'
            AND RPPV.PARAMETER_SEQNUM = 1
         OR     RPPV.PARAMETER_SEQNUM = 4
            AND RIP.rate_type_id = RT.rate_type_id
            AND RIP.rate_type_id = 1
            AND RIPD.RICODE = RIP.RICODE
            AND ZPD.GVCODE = RIP.GVCODE
            AND TZD.ZNCODE = RIP.ZNCODE
            AND TPD.TWCODE = RIP.TWCODE
            AND TTD.TTCODE = RIP.TTCODE
            AND ZP.GVCODE = RIP.GVCODE
            AND ZP.VSCODE = RIP.GVVSCODE
            AND ZP.ZNCODE = RIP.ZNCODE
            AND ZP.ZPCODE = ZD.ZPCODE
            AND RPEW.RATE_PACK_ENTRY_ID = RIP.RATE_PACK_ENTRY_ID
            AND RPPV.RATE_PACK_ELEMENT_ID = RPEW.RATE_PACK_ELEMENT_ID
            AND ZO.ZOCODE = ZP.ZOCODE
            AND RIPVS.RICODE = RIP.RICODE
            AND RIP.VSCODE = RIPVS.VSCODE
            AND RIPVS.VSDATE =
                   (SELECT MAX (RIPVS2.vsdate)
                      FROM MPURIVSD RIPVS2
                     WHERE RIPVS2.RICODE = RIPVS.RICODE AND RIPVS.STATUS = 'P')
ORDER BY RIPD.DES,
         TZD.DES,
         ZD.DIGITS,
         TTD.DES,
         RPPV.PARAMETER_ROWNUM,
         RPPV.PARAMETER_SEQNUM;

Open in new window

Chrißt'll ScholiersManager Pricing Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I don't understand what you are trying to escape.

For a "substring" just use double-ended wildcards:  %mysubstr%

If it is something fancy, maybe a regexp_like would be better suited.

If you need something different, please provide some sample data and expected results.
awking00Information Technology SpecialistCommented:
I have never really run into this issue but maybe the use of the "\" escape character is affecting these lines. Try removing them and see what happens -
AND TZD.DES NOT LIKE '%BASE Services%'
            AND TZD.DES NOT LIKE '%BASE01-Test%'
            AND TZD.DES NOT LIKE '%BASE02-Test%'
            AND TZD.DES NOT LIKE '%BASE03-Test%'
            AND TZD.DES NOT LIKE '%BASE010-Test%'
            AND TZD.DES NOT LIKE '%Premium Rate Calls%'
            AND TZD.DES NOT LIKE '%COYOTE%'
            AND TZD.DES NOT LIKE '%SMS PO%'
            AND TZD.DES NOT LIKE '%SMS PT%'
            AND TZD.DES NOT LIKE '%MMS PO%'
            AND TZD.DES NOT LIKE '%MMS PT%'
            AND TZD.DES NOT LIKE '%Imode Rate%'
            AND TZD.DES NOT LIKE '%BlackBerry%'
            AND TZD.DES NOT LIKE '%Intranet%'
            AND TZD.DES NOT LIKE '%internet over GPRS%'
_TAD_Commented:
I'm afraid your question is a bit vague and I am having trouble following what you are really asking for.  some explicit examples of what your query is returning, but need omitted would go a long way.

Also - just a note:

%  is a wildcard placeholder indicating 0 - N number of characters
_  is a wildcard placeholder indicating 1 character spot

You can also use regular expression matching.  This may be a little more "archaic", but could condense some of your code to make it easier to maintain.

http://www.techonthenet.com/oracle/regexp_like.php

            AND NOT REGEXP_LIKE (TZD.DES '^.BASE0[1-3]0?-Test.$'
            AND NOT REGEXP_LIKE (TZD.DES '^.[MS]MS P[OT].$
            AND TZD.DES NOT LIKE '%Premium Rate Calls%'
            AND TZD.DES NOT LIKE '%COYOTE%'
            AND TZD.DES NOT LIKE '%Imode Rate%'
            AND TZD.DES NOT LIKE '%BlackBerry%'
            AND TZD.DES NOT LIKE '%Intranet%'
            AND TZD.DES NOT LIKE '%internet over GPRS%'

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

Chrißt'll ScholiersManager Pricing Author Commented:
- Removing "\" helped running query

But the main problem where the brackets in the beginning of the "where" clause and the replacement of And to "Or" statement on "telephony%", as they were a bit interfering each other.

I'm rather new to SQL so syntax is sometimes an issue :(



WHERE (   (   (RIPD.DES) LIKE 'B-Fleet VPN Telephony%'
                       OR     RIPD.DES LIKE 'Telephony%'
                          AND (    RIPD.DES NOT LIKE 'Telephony RTC%'
                               AND RIPD.DES NOT LIKE 'Telephony ROC%'
                               AND RIPD.DES NOT LIKE 'Telephony Add-on%'
                               AND RIPD.DES NOT LIKE 'ZZ notUSED%'
                               AND RIPD.DES NOT LIKE 'Add-on%'
                               AND RIPD.DES NOT LIKE 'MMS Premium Originated%'
                               AND RIPD.DES NOT LIKE 'SMS Premium Originated%'
                               AND RIPD.DES NOT LIKE 'MMS Premium Terminated%')
                          AND TZD.DES NOT LIKE '%BASE Services%'
                          AND TZD.DES NOT LIKE '%BASE01-Test%'
                          AND TZD.DES NOT LIKE '%BASE02-Test%'
                          AND TZD.DES NOT LIKE '%BASE03-Test%'
                          AND TZD.DES NOT LIKE '%BASE010-Test%'
                          AND TZD.DES NOT LIKE '%Premium Rate Calls%'
                          AND TZD.DES NOT LIKE '%COYOTE%'
                          AND TZD.DES NOT LIKE '%SMS PO%'
                          AND TZD.DES NOT LIKE '%SMS PT%'
                          AND TZD.DES NOT LIKE '%MMS PO%'
                          AND TZD.DES NOT LIKE '%MMS PT%'
                          AND TZD.DES NOT LIKE '%Imode Rate%'
                          AND TZD.DES NOT LIKE '%BlackBerry%'
                          AND TZD.DES NOT LIKE '%Intranet%'
                          AND TZD.DES NOT LIKE '%internet over GPRS%'
                          AND TZD.DES NOT LIKE '%-Freephone-%'
                          AND TZD.DES NOT LIKE '%-Infokiosk-%'
                          AND TZD.DES NOT LIKE '%BANXAFE%')
AND ........
                                  AND RIPVS2.STATUS = 'P'))


second was this correctly separating (putting brackets) this clause from arguments above

 
 AND (   (RPPV.PARAMETER_SEQNUM) = 1
                       OR (RPPV.PARAMETER_SEQNUM) = 4)
                  AND (    (RIP.RATE_TYPE_ID) = RT.RATE_TYPE_ID
                       AND (RIP.RATE_TYPE_ID) = 1)

Open in new window

slightwv (䄆 Netminder) Commented:
I missed the OR in the original SQL you posted.  It did look out of place.

Other than that we really can't help much with the logic of the SQL since we really don't know what your requirements are.

As far as the parenthesis location goes, yes, they are very important as they dictate how the logic works.

It really isn't an SQL issue as it is a math/logic issue.  It is the order of precedence of the operators and how they are evaluated:
https://en.wikipedia.org/wiki/Order_of_operations

consider:
a and b or c and d
(a and b) or (c and d)

These are unnecessary (RPPV.PARAMETER_SEQNUM)  since there isn't any true/false check in there.
awking00Information Technology SpecialistCommented:
Perhaps you can provide a literal description of what you want and don't want. For example -
"I only want records where ripd.des starts with B-Fleet VPN Telephony or starts with Telephony except for records that start with Telephony followed by RTC or ROC or Add-on ..., etc. and in all cases only records where ripvs2.status = P"
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.