CODEWITHSUNDEEP
sqlsql-serverselect
Petrik
Petrik

Reputation: 827

SQL Select WHERE Column NOT LIKE across multiple lines

I have got table that is looking like this: 

+------+-------+-------------+
ID_Loc  Type    Data
+------+-------+-------------+
ABC     RMKS    Hello
ABC     NAM     Joe Smith
ABD     NAM     Peter Hill
ABD     RMKS    Bye Bye
ABD     NAM     Freddy Tall
ABE     NAM     Loran Bennett
ABE     RMKS    Bye Bye
ABF     NAM     Liv Claris
ABF     RMKS    Bye Bye
+------+-------+-------------+

And I need to select all ID_Loc WHERE DATA NOT LIKE 'Hello'. When I tried:

SELECT distinct ID_loc FROM data_full WHERE DATA NOT LIKE '% Hello'

This also selects ID_Loc: 'ABC', which contains 'Hello' in Data. Also as this is going to affect quite a lot of or rows, would be nice if I can point query to only look at the rows were Type RMKS is used.

I am using MS SQL Server 2008

SQL fiddle address is: http://sqlfiddle.com/#!6/38130/6

Any help would be really appreciated.

Upvotes: 2

Views: 3958

Answers (5)

void
void

Reputation: 7880

a simple Solution with reverse logic, you need a where not exists

SELECT distinct t.ID_loc FROM data_full t 
where not exists (
   select 1 from data_full t2 
   where t2.ID_loc=t.ID_loc and t2.type='RMKS' and t2.data like '%Hello')
ORDER BY 1;

SQLFIDDLE DEMO

Upvotes: 0

Daniel E.
Daniel E.

Reputation: 2059

you need to remove the space, added the RMKS filter as well.

SELECT distinct ID_loc 
FROM data_full 
WHERE Type= 'RMKS'
and DATA NOT LIKE '%Hello'  --'%Hello%' if it can be between other text

EDIT: If ID_Loc,Type isn't unique:

SELECT distinct ID_loc 
FROM data_full 
WHERE ID_loc NOT IN (SELECT ID_loc FROM  data_full WHERE Type= 'RMKS'
and DATA LIKE '%Hello')

http://sqlfiddle.com/#!6/59e9a/8

Upvotes: 0

Tanner
Tanner

Reputation: 22733

Firstly remove the space from your LIKE clause and secondly, you can use EXISTS clauses to filter results that match your criteria:

SQL Fiddle Demo

Query:

SELECT DISTINCT t.ID_loc 
FROM data_full t
WHERE NOT EXISTS (SELECT ID_loc 
              FROM data_full
              WHERE Data LIKE '%Hello' AND ID_loc = t.ID_loc )
      AND 
      EXISTS (SELECT ID_loc 
              FROM data_full
              WHERE Type = 'RMKS' AND ID_loc = t.ID_loc )

Results:

| ID_loc |
|--------|
|    ABD |
|    ABE |
|    ABF |

Upvotes: 0

Ewan
Ewan

Reputation: 1087

You need to remove the space and filter by Type

SELECT distinct ID_loc 
FROM data_full 
WHERE DATA NOT LIKE '%Hello%' And [TYPE] = 'RMKS'

Upvotes: 0

Cristian Lupascu
Cristian Lupascu

Reputation: 40526

If you need to select the ID_Loc values for which there is no record matching the '%Hello' pattern, here's the query to do it:

SELECT ID_loc
FROM data_full 
group by ID_Loc
having max(case 
             when DATA LIKE '%Hello' then 1
             else 0
           end) = 0;

This is the result: http://sqlfiddle.com/#!6/38130/33

If you also need to apply the Type = 'RMKS' filter, you can do so in a WHERE clause (sqlfiddle):

SELECT ID_loc
FROM data_full 
where type = 'RMKS'
group by ID_Loc
having max(case 
             when DATA LIKE '%Hello' then 1
             else 0
           end) = 0;

Upvotes: 6

Related Questions