SQL WHERE filter-CodePudding

I suck at explaining this issue, but I hope someone understands.

How do I filter something out of a column if it meets just one criteria out of the whole value?

So in this example I want to filter out any value that includes "First Touch", even if the value has other SpecialNeedsTagNames as well. For example "First Touch, Do Not Contact, Self-Serve Pilot" etc..

    SELECT count(*) AS count
    FROM
      (select *
       from OperationalReporting.VW_Delivery
       WHERE DeliveryCountry in ('NO',
                         'FI',
                         'DK',
                         'SE',
                         'IS')) AS expr_qry
    WHERE "CountryOfUse" = 'Sweden'
    AND "OrderStatus" IN ('Booked',
                    'Order Placed')
  AND "IsDelivered" = 'false'
  AND "Model" NOT IN ('Model QW',
                  'Model PX',
                  'Model ZY')
  AND ((SalesAdvisorHomeStore like 'EU-SE%'
        OR CommisionabaleStore like 'EU-SE%')
       AND (SpecialNeedsTagName is null
            or (SpecialNeedsTagName not like '%First Touch%')))
    ORDER BY count DESC
    LIMIT 50000;

Currently it only filters out the values that ONLY have "First Touch", as soon as it has a combined value it still outputs in the column.Faulty values

CodePudding user response：

So first things first, you may want to unify all values in you column by giving UPPER or LOWER. The where statement would look like this:

WHERE UPPER(SpecialNeedsTagName) LIKE 'FIRST TOUCH%'

If you want to make sure that there are no nulls add AND IS NOT NULL. This where will return you all of results that starts with FIRST TOUCH. If you are expecting that this phrase may occure in the middle of string add % at the beginning ('%FIRST TOUCH%')

CodePudding user response：

Try This :

SELECT count(*) AS count
    FROM
      (select *
       from XX.XX_XX
    WHERE NOT EXISTS (select * from XX.XX_XX where UPPER(SpecialNeedsTagName) 
    LIKE '%First Touch%'))
    ORDER BY count DESC
    LIMIT 50000;