mysql query to return values where a column doesn't contain specific value

Question

I'm relatively new to mysql. I'm trying to do a mysql query to return values where the table column value doesn't contain 'u'. I've tried several things, but it is returning 0 rows instead of 1000+, which it should. Right now, my SW_Ver_update_status column has all null initialized in it.

This is what I'm trying so far:

SELECT Distinct node_ipv4, model FROM Nokia_Network_Analyzer.NAC_DSLAMS 
WHERE vendor = 'Calix' AND model in ('C7','E7') AND  SW_Ver_update_status <> 
'u' LIMIT 100;

This is my table structure, and yes, it is quite populated, with about 2000:

element_key int(20) YES     
node_ipv4   varchar(25) YES     
node_ipv6   varchar(25) YES     
nodealias   varchar(25) NO  PRI 
fqdn    int(5)  YES     
region  varchar(3)  YES     
class   int(7)  YES     
service_tag int(5)  YES     
domain  varchar(15) YES     
active  int(2)  YES     
state   varchar(2)  YES     
LastUpdateUser  varchar(15) YES     
date_updated    datetime    YES     
date_created    datetime    YES     
vendor  varchar(15) YES     
model   varchar(15) YES     
software_version    varchar(20) YES     
head_node_monitor_pt    varchar(15) YES     
master_status   varchar(15) YES     
ping_reply  int(2)  YES     
LastUpdateIP    varchar(25) YES     
extra   int(2)  YES     
SW_Ver_update_status    varchar(1)  YES     
SW_ver_update_time  timestamp   YES

It looks like I'm doing it ok based on what I'm searching for online: mysql <> The <> part is new, and before that it worked fine. Also, the SW_Ver_update_status, and SW_ver_update_time columns are new to the table.

imgurl=https://www.w3resource.com/w3r_images/example-mysql-not-equal-to.gif&imgrefurl=https://www.w3resource.com/mysql/comparision-functions-and-operators/not-equal-operator.php&h=168&w=573&tbnid=HwUlkWoO0-iCcM:&tbnh=61&tbnw=211&usg=__grwdkFtcmXwkG2dXbmo-X_0MQqc%3D&vet=10ahUKEwj1tMeX2JHaAhUC7GMKHbvLBg4Q9QEILTAA..i&docid=O4oNupGqNj6QpM&sa=X&ved=0ahUKEwj1tMeX2JHaAhUC7GMKHbvLBg4Q9QEILTAA

Example table data:

    element_key  node_ipv4  node_ipv6  nodealias fqdn  region  class  service_tag  domain  active  state LastUpdateUser  date_updated  date_created  vendor  model  software_version  head_node_monitor_pt  master_status   ping_reply  LastUpdateIP  extra   SW_Ver_update_status  SW_ver_update_time
    null      10.xx.xxx.xx  null    BRFRFLIR123 null    FL  90712       SouthEast   null    FL  null    2018-02-20 07:48:42 2018-02-20 07:48:42 Calix   C7  1.0.320.669 empty   master  1   null  null  null  null

This is not correct create table syntex, also add 1 insert query. — Alpesh Jikadra, Mar 29 '18 at 14:41
The create table was a very long time ago and I don't have that info. It was created in phpMyAdmin. The data is in the table already as well. We don't need to insert the data or create the table again. I'm just trying to figure out why the query isn't working. I can give an example of data in the table if that will help, above. — Michele, Mar 29 '18 at 14:41
@AlpeshJikadra What is the point? He/She seems to want to know how to use the `LIKE` and `NOT LIKE` keywords. He/She just doesn't know about them yet. You don't need the entire database or the insert queries for that. — Tyler, Mar 29 '18 at 14:46
Possible duplicate of [SQL not displaying null values on a not equals query?](https://stackoverflow.com/questions/8036691/sql-not-displaying-null-values-on-a-not-equals-query) `null <> 'u'` evaluates to **false**. — Marc L., Mar 29 '18 at 15:19

Tyler · Accepted Answer · 2018-03-29T15:26:17.710

1

The NOT EQUAL operator works in full values.

"abc" <> "a" #returns true 
"abc" <> "abc" #returns false

From what I understand, you want to check if your does NOT contain a certain character. You are looking for LIKE.

LIKE by default will work similar to <>

"abc" LIKE "abc" #returns true
"abc" LIKE "b" #returns false

By using wildcards you can get the functionality you want.

"abc" LIKE "%b%" #returns true

The % says "match any number of characters". By placing a wild card at the start and end, we can check if a string contains a character or phrases at the start, the middle, or the end of the varchar.

So far I've shown you how to check if a phrase/character exists in another phrase/character. To check if a phrase/character does NOT exist, you can use the NOT keyword.

"abc" NOT LIKE "%b%" #returns false
"ac" NOT LIKE "%b%" #returns true

Full example:

SELECT * FROM yourTable WHERE "abc" NOT LIKE "%b%"
SELECT * FROM yourTable WHERE "ac" NOT LIKE "%b%"

The first query will return nothing, the second query will return everything.

As mentioned by Dionei Miodutzki, if you are working with nulls you will need to check for nulls as well.

SELECT * FROM `tableName` WHERE `columnName` IS NOT NULL && `columnName` NOT LIKE '%u%'

Read more on LIKE and NOT LIKE at documentation and W3Schools.

edited Mar 29 '18 at 15:26

answered Mar 29 '18 at 14:44

Tyler

957
11
27

I'm not sure. I tried that and it returns 0 rows: SELECT node_ipv4, model FROM NAC_DSLAMS WHERE vendor = 'Calix' AND model in ('C7','E7') AND SW_Ver_update_status NOT LIKE "%u%" LIMIT 100; – Michele Mar 29 '18 at 14:59
I've tried ...SW_Ver_update_status NOT IN ('u') LIMIT 100 as well. Could it be the null initialization? – Michele Mar 29 '18 at 15:02
@Michele Can you quickly try `SELECT * FROM Nokia_Network_Analyzer.NAC_DSLAMS WHERE vendor = 'Calix'` and tell me if it returns any rows? – Tyler Mar 29 '18 at 15:06
1

the like and not like operators doesnt work for null values. You should use "is not null" for null values – Dionei Miodutzki Mar 29 '18 at 15:07
1

@DioneiMiodutzki Ah, thank you for the notice. I will update my answer as soon as I get a working example together. – Tyler Mar 29 '18 at 15:11
I changed my table so I don't initialize to null for SW_Ver_update_status and it works now. Returned 100. Thanks a lot! :) – Michele Mar 29 '18 at 15:22

mysql query to return values where a column doesn't contain specific value

1 Answers1