SQL SELECT * FROM table WHERE column NOT LIKE '%str% 'isn't working, i feel kind of stupid

Question

i got kind of upset about my inability to get this to work and since I've already wasted half a day on this rather simple problem I'll give up on it and ask you guys.

So i have a SQL table where the last column is named flag:

name:  type:          collation:           null: default:
...
flag   varchar(250)   utf8mb4_0900_ai_ci   yes   NULL

The data has either double or NULL as value and i want to select all the data which isn't double. But no operator gives any result, like:

SELECT * FROM `table` WHERE `flag` NOT LIKE 'double';
SELECT * FROM `table` WHERE `flag` NOT LIKE '%double%';
SELECT * FROM `table` WHERE `flag` NOT LIKE '%d%';
SELECT * FROM `table` WHERE `flag` != 'double';
SELECT * FROM `table` WHERE NOT flag = 'double';

i checked every typo, there are no strange/hidden symbols in the data. Of course i could do a
SELECT * FROM table WHERE flag IS NULL which works atm, but there will be other flags > not useful.

Any clue what's the problem here?

mysql status:
--------------
mysql  Ver 8.0.37-0ubuntu0.22.04.3 for Linux on x86_64 ((Ubuntu))

Connection id:          934
Current database:
Current user:           user@localhost
SSL:                    Not in use
Current pager:          less
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.37-0ubuntu0.22.04.3 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal
Uptime:                 1 hour 12 min 57 sec

DB type i InnoDB

Answer 1

• The LIKE operator only operates on non-NULL values.
• You need to use IS NULL / IS NOT NULL to deal with NULL separately.
  • More modern RDBMS will support (the verbose) IS DISTINCT FROM operator, which can work with NULL operands, but only performs an equality-comparison, not LIKE. I assume your build of MySQL doesn't support this, however.
• But all that's moot anyway, because you aren't using LIKE for pattern-matching: you seem to only be performing value-(in)equality.
• So use <>, like so:

SELECT
    *
FROM
    `table`
WHERE
    `flag` IS NULL
    OR
    `flag` <> 'double';

---

UPDATE: I forgot that MySQL does implement IS DISTINCT FROM but with the spaceship operator: <=>.