Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→by datatype
| Line 74: | Line 74: | ||
=== by datatype === | === by datatype === | ||
==== VARCHAR and NOT allows NULL value ==== | |||
Using NULLIF() function<ref>[https://www.w3schools.com/sql/func_mysql_nullif.asp MySQL NULLIF() Function]</ref> | |||
SQL query: | |||
<pre> | |||
SELECT NULLIF(TRIM(`my_column`), "") | |||
</pre> | |||
Example result: | |||
<pre> | |||
SELECT NULLIF(null, ""); | |||
-- return NULL | |||
SELECT NULLIF("", ""); | |||
-- return NULL | |||
SELECT NULLIF(TRIM(" "), ""); | |||
-- return NULL | |||
SELECT NULLIF(TRIM("not empty string "), ""); | |||
-- return "not empty string" | |||
</pre> | |||
==== VARCHAR and allows NULL value ==== | ==== VARCHAR and allows NULL value ==== | ||
<table border="1" style="width: 100%"> | <table border="1" style="width: 100%"> | ||