CODEWITHSUNDEEP
javamysql
user2176576
user2176576

Reputation: 756

mysql treat a column field as null if it is blank

Hi this is the table I am querying:

mytable
id, name
1,name1
2,name2
3,' ' //this is a white space
4,null

but when i use this query to set blank columns to null:

update mytable set `name`= CASE `name` WHEN '' THEN NULL ELSE `name` END;

It updates even the column field which has space in it. so the result is :

1,name1
2,name2
3,null
4,null

Why does mysql treat whitespace also as '' blank? Is there any option to replace only blanks in the table to null and let the whitespaces be whitespaces?

Upvotes: 2

Views: 1430

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

Because MySQL treats '', ' ' or even ' ' as the same.

Try querying:

select '' = '             '

It will return 1 that means true. So, MySQL treats all the whitespace strings of any length as equals.

If you want to update the rows with 0 length empty string, use Length function:

UPDATE mytable 
SET 
    `name` = NULL
WHERE
    `name` = '' AND LENGTH(`name`) = 0;

Upvotes: 3

BalaMurugan.N
BalaMurugan.N

Reputation: 118

nullif( trim(name) , '') is NULL

https://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_nullif

try this..:)

Upvotes: 2

Related Questions