select query using multiple like conditions

Question

Below is an existing ms sql server 2008 report query.

SELECT
    number, batchtype, customer, systemmonth, systemyear, entered, comment, totalpaid
FROM
    payhistory LEFT OUTER JOIN agency ON
        payhistory.SendingID = agency.agencyid      
WHERE
    payhistory.batchtype LIKE 'p%' AND
    payhistory.entered >= '2011-08-01 00:00:00.00' AND
    payhistory.entered <  '2011-08-15 00:00:00.00' AND
    payhistory.systemmonth = 8 AND
    payhistory.systemyear = 2011 AND
    payhistory.comment NOT LIKE 'Elit%'

Results will look like this:

number  batchtype   customer    systemmonth systemyear  entered     comment         totalpaid
6255756 PC      EMC1106     8       2011        12:00:00 AM DP From - NO CASH       33
5575317 PA      ERS002      8       2011        12:00:00 AM MO-0051381526 7/31      20
6227031 PA      FTS1104     8       2011        12:00:00 AM MO-10422682168 7/30     25
6232589 PC      FTS1104     8       2011        12:00:00 AM DP From - NO CASH       103
2548281 PC      WAP1001     8       2011        12:00:00 AM NCO DP $1,445.41        89.41
4544785 PCR     WAP1001     8       2011        12:00:00 AM NCO DP $1,445.41        39

What I am trying to do is modify the query that will exclude records where the customer is like 'FTS%' and 'EMC%' and batchtype = 'PC'. As you can see in the result set there are records where customer is like FTS% and batchtype = 'PA'. I would like to keep these records in the results. I would appreciate any ideas offered.

So, why do you say "only when btype = 'PC'" and then put `btype <> 'PC'`? — Lamak, Oct 13 '11 at 17:56

score 4 · Accepted Answer · edited May 23 '17 at 12:25

Your query contains a mix of upper and lower string comparison targets. As far as I'm aware, SQL Server is not by default case-sensitive; is it possible this is what is tripping your query up? Check collation per this answer.

EDIT: Based on your updated question, can you not just use an AND clause that uses a NOT on the front? In other words, add a 'AND not (x)' clause, where 'x' is the conditions that define the records you want to exclude? You'd need to nest the customer test, because it's an OR. e.g.:

... payhistory.comment NOT LIKE 'Elit%'
AND not ((customer like 'FTS%' or customer like 'EMC%') AND batchtype = 'PC')

As a side note, I believe that a LIKE clause may imply an inefficient table scan in some (but not all) cases, so if this query will be used in a performance-sensitive role you may want to check the query plan, and optimise the table to suit.

YES!! That is exactly what I needed to do. I spent too much time trying to put that statement together, not knowing I can use the "NOT" argument to filter out those records. Thanks to all!! — user992322, Oct 14 '11 at 15:48
Great! Also - if it answers your question, can you mark it as an answer? — Geoff, Oct 14 '11 at 17:29

score 3 · Answer 2 · edited Apr 04 '16 at 13:14

3

$sql="select * from builder_property where builder_pro_name LIKE '%%' OR builder_pro_name LIKE '%za%' AND status='Active'";

This will return all the builder property name in table that will ends name like plaza or complex.

edited Apr 04 '16 at 13:14

Avión

7,963
11
64
105

answered Dec 25 '13 at 10:33

Anki007

401
5
6

score 0 · Answer 3 · answered Oct 13 '11 at 17:57

It can be because your sever might be case sensitive. In that case, below query would work.

SELECT
table1.number, table1.btype, table1.cust, table1.comment, table2.ACode
FROM
table1 LEFT OUTER JOIN table2 ON table1.1ID = table2.2ID
WHERE
lower(table1.btype) LIKE 'p%' AND
lower(table1.comment) NOT LIKE 'yyy%' AND
lower(table1.cust) NOT LIKE 'abc%' AND
lower(table1.cust) NOT LIKE 'xyz%' AND
lower(table1.btype) <> 'pc'

score 0 · Answer 4 · answered Oct 13 '11 at 22:06

Add this condition to the WHERE clause:

NOT((customer LIKE 'FTS%' OR customer LIKE 'EMC%') AND batchtype='PC')

Assuming your other results are OK and you just want to filter those out, the whole query would be

SELECT
    number, batchtype, customer, systemmonth, systemyear, entered, comment, totalpaid
FROM
    payhistory 
    LEFT OUTER JOIN agency ON
        payhistory.SendingID = agency.agencyid      
WHERE
    payhistory.batchtype LIKE 'p%' AND
    payhistory.entered >= '2011-08-01 00:00:00.00' AND
    payhistory.entered <  '2011-08-15 00:00:00.00' AND
    payhistory.systemmonth = 8 AND
    payhistory.systemyear = 2011 AND
    payhistory.comment NOT LIKE 'Elit%' AND
    NOT((payhistory.customer LIKE 'FTS%' OR payhistory.customer LIKE 'EMC%') AND payhistory.batchtype='PC')

Hope that works for you.

Michael Riley - AKA Gunny · Answer 5 · 2011-10-14T14:36:22.133

When building complex where clauses it is a good idea to use parenthesis to keep everything straight. Also when using multiple NOT LIKE statements you have to combine all of the NOT LIKE conditions together using ORs and wrap them inside of a separate AND condition like this...


WHERE     
    (payhistory.batchtype LIKE 'p%')
AND (payhistory.entered >= '2011-08-01 00:00:00.00')
AND (payhistory.entered <  '2011-08-15 00:00:00.00')
AND (payhistory.systemmonth = 8 )
AND (payhistory.systemyear = 2011)
AND ( // BEGIN NOT LIKE CODE

    (payhistory.comment NOT LIKE 'Elit%') 

OR  (
    (payhistory.customer NOT LIKE 'EMC%') AND 
    (payhistory.batchtype = 'PC')
    ) 

OR  (
    (payhistory.customer NOT LIKE 'FTS%') AND 
    (payhistory.batchtype = 'PC')
    )

    ) //END NOT LIKE CODE

select query using multiple like conditions

5 Answers5