0

Example Table (Person_data) 

Name   |   Area      |   Id
=============================== 
Jack   | A_102       |  102   
Roy    | Abc-34      |  109   
tom    | ABC6778     |  107    
Aly    | hj23        |  122   
Saly   | dsfds       |  342

I need a query such that it returns all the rows where Area column doesn't contain _ or - or Abc or ABC.

My query

Select * from Person_data 
where area not like '%-%' 
or area not like '%_%' 
or area not like '%Abc%' 
or area not like '%ABC%';
nbrooks
  • 18,126
  • 5
  • 54
  • 66
2FaceMan
  • 443
  • 2
  • 18
  • 34

5 Answers5

2

returns all the rows where Area column doesn't contain '_' or '-' or 'Abc' or 'ABC'

You need to combine these search conditions with AND's, but the problem is the _ which is a a reserved wildcard in the LIKE predicate. You have to escape it in the LIKE predicate using [] or you can determine any escape character to use to escape it using the ESCAPE keyword something like LIKE '%\_%' ESCAPE '\', therefore it will treated like a literal, and it is the standard way to that like so:

SELECT * 
FROM PersonData
WHERE area NOT LIKE '%-%'
AND area NOT LIKE '%\_%' ESCAPE '\'
AND area NOT LIKE '%Abc%' 
AND area NOT LIKE '%ABC%';

Here is a SQL Fiddle demo

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
  • 1
    @Mahmoud - quick SQL Fiddle tip - when OP provides sample data in some kind of regular format (such as this one) you can copy and paste that data into SQL Fiddle's "Text to DDL" tool to quickly build a database table using that data. For example, in this case, here's what it would have produced: http://sqlfiddle.com/#!3/8480a/1 (Should make for answering these questions less tedious). Cheers! – Jake Feasel Oct 02 '12 at 03:21
  • @JakeFeasel - First of all, Thank you very very much for SQL Fiddle, I always wish to thank you for it. Secondly thanks for the tip. Great work. – Mahmoud Gamal Oct 02 '12 at 05:09
  • It's my pleasure - keep up the good work helping people with their database problems! – Jake Feasel Oct 02 '12 at 06:11
2
SELECT * 
FROM PersonData
WHERE area NOT LIKE '%-%'
    AND area NOT LIKE '%[_]%' -- brackets for special character matching
    AND area NOT LIKE '%Abc%' 
    AND area NOT LIKE '%ABC%';

As others have mentioned, you probably want AND operators rather than OR operators since your current query looks like it would probably return anything that's non-null.

Also, you must escape or bracket the matching of a literal underscore _ as that is a special wildcard character in SQL Server (NOT LIKE '%_%' is semantically identical to LIKE '').

Another interesting thing here is the use of both Abc and ABC, which is only necessary if you need case-sensitivity.

Community
  • 1
  • 1
Tim Lehner
  • 14,813
  • 4
  • 59
  • 76
0

You didn't really ask a question, but I think you want your "ORs" to be "ANDs".

Adam Plocher
  • 13,994
  • 6
  • 46
  • 79
0

Replace OR with AND. Now it gets all the rows which don't contain at least one of the symbols.

sashkello
  • 17,306
  • 24
  • 81
  • 109
0

You need to use an AND conditions for your WHERE clause instead of OR conditions:

SELECT * 
FROM `Person_data` 
WHERE `area` NOT LIKE '%-%' 
AND `area` NOT LIKE '%_%' 
AND `area` NOT LIKE '%Abc%' 
AND `area` NOT LIKE '%ABC%';
doublesharp
  • 26,888
  • 6
  • 52
  • 73