SQL Like with multiple statements

Question

So I have the following query which does not give back the right results and I do not know why:

Sample data:

LIST1 | LIST2
APRIL | NOLA
NOLA  | BEBB
NOLA  | APROLS
APRA  | BLIN

The query:

SELECT LIST1, 
       LIST2, 
       CASE WHEN (([LIST1] LIKE '%APR%' 
                   OR [LIST1] LIKE '%NOLA%') 
             AND ([LIST2] NOT LIKE '%APR%' 
                   AND [LIST2] NOT LIKE '%NOLA%')) 
            THEN 1 
            ELSE 0 
       END AS RESULTS

It gives back 1 for both rows. It should give back 0 for the first row. So I would expect the following output

LIST1 | LIST2 | RESULTS
APRIL | NOLA  | 0
NOLA  | BEBB  | 1
NOLA  | APROLS| 0
APRA  | BLIN  | 1

What is my mistake here? The output should be 0 for those two cases as I have clearly put in an 'AND' statement.

Add some more sample table data and adjust the expected result accordingly. Also explain why you want that result. — jarlh
– jarlh, Commented Aug 27, 2018 at 9:46
The query looks fine and should give you the expected result. This SQL fiddle proves that: sqlfiddle.com/#!18/bc8cf/1 — Thorsten Kettner
– Thorsten Kettner, Commented Aug 27, 2018 at 11:16
In my query, the last AND was an OR. Someone edited it and I accepted without seeing it as I probably thought they edited only the format — mrPy
– mrPy, Commented Aug 28, 2018 at 12:27

Zaynul Abadin Tuhin · Accepted Answer · 2018-08-27 09:57:30Z

1

SELECT LIST1, 
       LIST2, 
       CASE 
          WHEN (([LIST1] LIKE '%APR%' 
                 OR [LIST1] LIKE '%NOLA%') 
           OR ([LIST2] NOT LIKE '%APR%' 
                AND [LIST2] NOT LIKE '%NOLA%')) 
          THEN 1 
          ELSE 0 
       END AS RESULTS

LIST1   LIST2   RESULTS
APRIL   NOLA    1
NOLA    BEBB    1
NOLA    APROLS  1
APRA    BLIN    1

it gives you for both case 1 because [LIST1] LIKE '%APR%' OR [LIST1] LIKE '%NOLA%' this line is true for 'APRIL' of 1st row value of list1 and again it is true for 'NOLA' for 2nd row and when any portion of will true of or condition then full condition will be true. As a result you got both case 1

but if you put and condition then result will be as your expectation

SELECT LIST1, 
       LIST2, 
       CASE WHEN (
         (
           [LIST1] LIKE '%APR%' 
                   OR [LIST1] LIKE '%NOLA%') 
             AND 
         (
                  [LIST2] NOT LIKE '%APR%' 
                   AND [LIST2] NOT LIKE '%NOLA%')

          ) 
            THEN 1 
            ELSE 0 
       END AS RESULTS from t

http://sqlfiddle.com/#!18/9f8c5/1

LIST1   LIST2   RESULTS
APRIL   NOLA    0
NOLA    BEBB    1
NOLA    APROLS  0
APRA    BLIN    1

edited Aug 27, 2018 at 9:57

answered Aug 27, 2018 at 9:39

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mrPy Over a year ago

But I also included an 'AND' statement. Does it just disregard it? So what should I change if I would like the desired output?

Zaynul Abadin Tuhin Over a year ago

@mrPy sqlfiddle.com/#!18/0d7a4/1 could you check this please does it ok?

Collectives™ on Stack Overflow

SQL Like with multiple statements

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related