CODEWITHSUNDEEP
sqlmysqlsql-like
melonlemon
melonlemon

Reputation: 3

SQL SELECT * FROM table WHERE column NOT LIKE '%str% '

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.

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.

SELECT * FROM table WHERE flag IS NULL

works atm, but there will be other flags > not useful.

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

Upvotes: 0

Views: 85

Answers (1)

Dai
Dai

Reputation: 155568

  • The LIKE operator only operates on non-NULL values.
  • You need to use IS NULL / IS NOT NULL to deal with NULL separately.
  • 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: <=>.

Upvotes: 2

Related Questions