0

I have a column that has numbers like this

p123
p139
d291
c9384
p393
null
null
c148

I want to compare this column where it says it includes only the p and null values. My code is:

WHERE (COLUMN NOT LIKE 'C%' AND COLUMN NOT LIKE 'D%' OR COLUMN IS NULL)

WHEN I have the condition COLUMN NOT LIKE 'C%' AND COLUMN NOT LIKE D% it comes back with out the null, but when I add the is null value, it comes back with one a c column even though there is a comparison to not include ones with c. I don't know how to get the order right or what parenthesis to use.

  • You say `includes only the p` but you do `NOT LIKE 'C%' AND COLUMN NOT LIKE D%`. What is correct? So what if there was another record `A123`, should it be included or not? – Tim Schmelter Nov 22 '14 at 00:30
  • Yes. There could be a case like that and it would be included. We just know we don't want c and d but don't want to exclude null. –  Nov 22 '14 at 01:39
  • This should work (after you fix the 'D%'). Please show a test case failing at http://sqlfiddle.com/#!6 . – philipxy Nov 22 '14 at 04:43

2 Answers2

0

It should be:

 WHERE COLUMN LIKE 'P%' OR COLUMN IS NULL

Regarding your comment to my answer, if what you want is all values that doesn't match "D" or "C" in the first character, then the WHERE conditional is this:

WHERE (COLUMN NOT LIKE 'C%' AND COLUMN NOT LIKE 'D%') OR COLUMN IS NULL
Verhaeren
  • 1,661
  • 9
  • 10
  • What if there were other rows that had other beginning letters than p and you wanted them too? Why does it still include a c value if you say not like. –  Nov 22 '14 at 01:43
  • @epv I don't get what you're saying. I built that where clausule according with this: "it includes only the p and null values". You want to collect also values starting with letter "c"? – Verhaeren Nov 22 '14 at 01:57
0

You could use a wildcard:

where [column] not like '[cd]%' or [column] is null

this would exclude any rows beginning with eithercord. In your example the three rows starting withpand the two rows withnullwould be returned.

jpw
  • 44,361
  • 6
  • 66
  • 86