132

I have the following query

SELECT * FROM table
WHERE tester <> 'username';

I am expecting this to return all the results where tester is not the string username, But this not working. I think I am looking for the inverse of the Like operator but I am not sure? In my searches I have found solutions for numbers (that's where i got <> from), but this seems to not be working with strings.

Dan Ciborowski - MSFT
  • 6,432
  • 8
  • 50
  • 82
  • 5
    Are the values you are having problems with `NULL` values? (`NULL <> 'username'` => `NULL` => false)? – Wrikken May 01 '13 at 18:59

6 Answers6

196

Your where clause will return all rows where tester does not match username AND where tester is not null.

If you want to include NULLs, try:

where tester <> 'username' or tester is null

If you are looking for strings that do not contain the word "username" as a substring, then like can be used:

where tester not like '%username%'
Gordon Linoff
  • 1,198,228
  • 53
  • 572
  • 709
  • keep in mind that "like" has performance costs https://stackoverflow.com/questions/6142235/sql-like-vs-performance – 15412s Oct 01 '21 at 09:05
45

Try the following query

select * from table
where NOT (tester = 'username')
Dan Ciborowski - MSFT
  • 6,432
  • 8
  • 50
  • 82
Chris
  • 2,959
  • 1
  • 28
  • 42
25

NULL-safe condition would looks like:

select * from table
where NOT (tester <=> 'username')
Viktor Zeman
  • 449
  • 5
  • 5
  • Yes!, this is the only thing that works for me, because I have a chain of and's. Didn't know the <=> operator. Thanks! – varta Oct 11 '18 at 14:00
  • 1
    Just noticed that the `<=>` operator only exists in the `MySQL` world, for more info see [what is <=>](https://stackoverflow.com/questions/21927117/what-is-this-operator-in-mysql) – Top-Master Apr 27 '19 at 06:18
8
select * from table
where tester NOT LIKE '%username%';
Ömer Faruk Almalı
  • 3,762
  • 6
  • 35
  • 62
7

The strcomp function may be appropriate here (returns 0 when strings are identical):

 SELECT * from table WHERE Strcmp(user, testername) <> 0;
shA.t
  • 15,880
  • 5
  • 49
  • 104
user3088463
  • 91
  • 1
  • 6
2

Another way of getting the results 

SELECT * from table WHERE SUBSTRING(tester, 1, 8)  <> 'username' or tester is null
karthik kasubha
  • 388
  • 2
  • 12