SQL: How to perform string does not equal

Question

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.

Are the values you are having problems with `NULL` values? (`NULL <> 'username'` => `NULL` => false)? — Wrikken, May 01 '13 at 18:59

Gordon Linoff · Accepted Answer · 2013-05-01T19:08:28.337

217

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%'

edited May 01 '13 at 19:08

answered May 01 '13 at 18:58

Gordon Linoff

1,242,037
58
646
786

keep in mind that "like" has performance costs https://stackoverflow.com/questions/6142235/sql-like-vs-performance – 15412s Oct 01 '21 at 09:05
I really wonder why the MySQL developers did not implement `!=` that you find in most of the programming languages today. – Avatar Nov 10 '22 at 15:26
It was quite confusing checking column for != 'string' while having rows with NULL in column but with OR IS NULL it works – zyrup Apr 19 '23 at 23:09

score 44 · Answer 2 · edited May 01 '13 at 19:08

44

Try the following query

select * from table
where NOT (tester = 'username')

edited May 01 '13 at 19:08

Dan Ciborowski - MSFT

6,807
10
53
88

answered May 01 '13 at 18:58

Chris

2,955
1
30
43

This one doesn't include the null values in my case (postgresql) – MatterOfFact Nov 27 '22 at 13:15

score 27 · Answer 3 · answered Sep 01 '16 at 18:00

27

NULL-safe condition would looks like:

select * from table
where NOT (tester <=> 'username')

answered Sep 01 '16 at 18:00

Viktor Zeman

489
5
6

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

Ömer Faruk Almalı · Answer 4 · 2013-05-01T19:06:37.420

8

select * from table
where tester NOT LIKE '%username%';

edited May 01 '13 at 19:06

answered May 01 '13 at 19:00

Ömer Faruk Almalı

3,792
6
37
63

score 7 · Answer 5 · edited May 18 '15 at 12:32

7

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

 SELECT * from table WHERE Strcmp(user, testername) <> 0;

edited May 18 '15 at 12:32

shA.t

16,580
5
54
111

answered Jun 18 '14 at 04:37

user3088463

91
1
6

score 2 · Answer 6 · answered Aug 27 '19 at 06:00

2

Another way of getting the results

SELECT * from table WHERE SUBSTRING(tester, 1, 8)  <> 'username' or tester is null

answered Aug 27 '19 at 06:00

karthik kasubha

392
2
13

SQL: How to perform string does not equal

6 Answers6

Linked