Retrieving Values when using NOT LIKE

  • cjeans

    SSC Enthusiast

    Points: 107

    Hi,

    I am trying to filter values in my database and am currently looking to provide all claims with a blank description in the Reason field and the claim type is not related to Taxis or Parking. The query i have for this is:

    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%'

    However this is not retrieving the correct values. Can you help me identify where this has gone wrong?

    Thanks,

  • Thom A

    SSC Guru

    Points: 98305

    cjeans - Friday, August 24, 2018 5:30 AM

    However this is not retrieving the correct values. Can you help me identify where this has gone wrong?

    What's incorrect about the data you are receiving? Can you provide sample data and expected results, along with the behaviour you're getting? Don't forget, we can't see your data, so we can't run your query to get the results.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Jonathan AC Roberts

    SSCoach

    Points: 16844

    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%'
    I think you need to add some brackets:
    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND (SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%')
    Or one of these:
    IF OBJECT_ID('tempdb..#Table_1','U') IS NOT NULL DROP TABLE #Table_1
    SELECT * INTO #Table_1
    FROM (VALUES('abc','my taxi dfg parking sdf'),
    ('abc','my taxi dfg sdf'),
    ('abc','my dfg parking sdf'),
    ('abc','my sdf'),
    ('','my taxi dfg parking sdf'),
    ('','my taxi dfg sdf'),
    ('','my dfg parking sdf'),
    ('','my sdf')
    ) T(Reason,SubTypeDesc)

    SELECT * FROM #Table_1
    SELECT * FROM #Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%'
    SELECT * FROM #Table_1 WHERE Reason = '' AND (SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%')
    SELECT * FROM #Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' AND SubTypeDesc NOT LIKE '%Parking%'

  • cjeans

    SSC Enthusiast

    Points: 107

    Thom A - Friday, August 24, 2018 5:35 AM

    cjeans - Friday, August 24, 2018 5:30 AM

    However this is not retrieving the correct values. Can you help me identify where this has gone wrong?

    What's incorrect about the data you are receiving? Can you provide sample data and expected results, along with the behaviour you're getting? Don't forget, we can't see your data, so we can't run your query to get the results.

    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND (SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE 'Parking%')

    With this i still have values in the SubTypeDesc column that is 'Parking - car park'. Everything else is correct.

  • Thom A

    SSC Guru

    Points: 98305

    cjeans - Friday, August 24, 2018 7:23 AM

    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND (SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE 'Parking%')

    With this i still have values in the SubTypeDesc column that is 'Parking - car park'. Everything else is correct.

    That makes sense. "Parking" is not like "Taxi" and "Taxi" is not like "Parking". If would be like having the clause:
    WHERE  i <> 1 OR i <> 2
    If i has a value of 1, then it's not 2 (so the statement is true), if i has a value of 2 then it's not 1 (so the statement is true). If i
    has a value of 3, then it;s not 1 or 2 (so the statement is true).

    You need to use AND:
    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' AND SubTypeDesc NOT LIKE 'Parking%'

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • drew.allen

    SSC Guru

    Points: 76536

    cjeans - Friday, August 24, 2018 5:30 AM

    Hi,

    I am trying to filter values in my database and am currently looking to provide all claims with a blank description in the Reason field and the claim type is not related to Taxis or Parking. The query i have for this is:

    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%'

    However this is not retrieving the correct values. Can you help me identify where this has gone wrong?

    Thanks,

    You've mistranslated your English description into conditional logic.  The proper (direct) translation is
    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND NOT ( SubTypeDesc  LIKE '%Taxi%' OR SubTypeDesc LIKE '%Parking%' )

    And when you distribute the NOT, you have to switch AND and OR, so this becomes

    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' AND SubTypeDesc NOT LIKE '%Parking%'

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Lynn Pettis

    SSC Guru

    Points: 442138

    drew.allen - Friday, August 24, 2018 7:36 AM

    cjeans - Friday, August 24, 2018 5:30 AM

    Hi,

    I am trying to filter values in my database and am currently looking to provide all claims with a blank description in the Reason field and the claim type is not related to Taxis or Parking. The query i have for this is:

    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' OR SubTypeDesc NOT LIKE '%Parking%'

    However this is not retrieving the correct values. Can you help me identify where this has gone wrong?

    Thanks,

    You've mistranslated your English description into conditional logic.  The proper (direct) translation is
    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND NOT ( SubTypeDesc  LIKE '%Taxi%' OR SubTypeDesc LIKE '%Parking%' )

    And when you distribute the NOT, you have to switch AND and OR, so this becomes

    SELECT * FROM dbo.Table_1 WHERE Reason = '' AND SubTypeDesc NOT LIKE '%Taxi%' AND SubTypeDesc NOT LIKE '%Parking%'

    Drew

    I guess they don't teach Boolean logic any more.

  • cjeans

    SSC Enthusiast

    Points: 107

    Thanks all. It worked by changing the OR to AND.

    Couldn't see the own fault in my logic -_-

  • Lynn Pettis

    SSC Guru

    Points: 442138

    cjeans - Wednesday, August 29, 2018 8:27 AM

    Thanks all. It worked by changing the OR to AND.

    Couldn't see the own fault in my logic -_-

    Unless you have specifically been taught Boolean logic, it can be tough to understand at first.  Once you learn it though, not to hard.
    I had the benefit of a microcircuits course in college many years ago where we needed to understand Boolean logic while working with AND, NAND, OR, NOR, XOR gates and such.

  • Thom A

    SSC Guru

    Points: 98305

    Lynn Pettis - Wednesday, August 29, 2018 9:06 AM

    Unless you have specifically been taught Boolean logic, it can be tough to understand at first.  Once you learn it though, not to hard.
    I had the benefit of a microcircuits course in college many years ago where we needed to understand Boolean logic while working with AND, NAND, OR, NOR, XOR gates and such.

    You have just reminded of my GCSE Electronics course, Lynn... I never wanted to be reminded of that. I swear, all we ever did (every lesson) was tests on boolean logic and resistor bands. Every school day for 2 years...  Oh, and threw magnets onto the metal ceiling. :hehe: (that part was fun at least).

    I suppose, at least it helped me with my boolean logic in computer languages (I can't remember my resistor band values at all though). 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Lynn Pettis

    SSC Guru

    Points: 442138

    Thom A - Wednesday, August 29, 2018 9:17 AM

    Lynn Pettis - Wednesday, August 29, 2018 9:06 AM

    Unless you have specifically been taught Boolean logic, it can be tough to understand at first.  Once you learn it though, not to hard.
    I had the benefit of a microcircuits course in college many years ago where we needed to understand Boolean logic while working with AND, NAND, OR, NOR, XOR gates and such.

    You have just reminded of my GCSE Electronics course, Lynn... I never wanted to be reminded of that. I swear, all we ever did (every lesson) was tests on boolean logic and resistor bands. Every school day for 2 years...  Oh, and threw magnets onto the metal ceiling. :hehe: (that part was fun at least).

    I suppose, at least it helped me with my boolean logic in computer languages (I can't remember my resistor band values at all though). 🙂

    I have found that learning Boolean logic has been vital to what I do.  It is amazing how many people not exposed to Boolean logic don't understand why some queries that look like they should work, don't.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply