Editing Data cleaning (section)

=== 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 ====
<table border="1" style="width: 100%">
  <tr style="background-color: #555555; color: #ffffff;">
    <td>data type of column</td>
    <td>possible column values</td>
    <td>method1: <br />find not fulfilled or empty values</td> 
    <td>method2: <br />find fulfilled and non-empty values</td>
    <td>method3: <br />find NULL values</td>
    <td>method4: <br />find not NULL values</td>
  </tr>
  <tr>
     <td rowspan="5">{{kbd | key = VARCHAR}} and allows {{kbd | key = NULL}}</td>
    <td>fulfilled value ex:123</td>
    <td></td> 
    <td>V</td>
    <td></td>
    <td>V</td> 
  </tr>
  <tr>
    <td>{{kbd | key = NULL}} type:null</td>
    <td>V</td> 
    <td></td>
    <td>V</td>
    <td></td> 
  </tr>
  <tr>
    <td>{{kbd | key = 'NULL'}} type:string</td>
    <td></td> 
    <td></td>
    <td></td>
    <td></td> 
  </tr>
  <tr>
    <td>0</td>
    <td></td> 
    <td>V</td>
    <td></td>
    <td>V</td> 
  </tr>
  <tr>
    <td>EMPTY ex: <nowiki>'' or space(s) '  '</nowiki></td>
    <td>V</td> 
    <td></td>
    <td></td>
    <td>V</td>
  </tr>
</table>

symbol V: means the column value will be able to find by means of the method


* method1: 
** {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE COALESCE(column_name, '') = ''</nowiki>}}<ref>[http://www.w3resource.com/mysql/comparision-functions-and-operators/coalesce-function.php MySQL COALESCE() function - w3resource]</ref>
** {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE column_name IS NULL OR LENGTH(TRIM( column_name )) = 0</nowiki>}}
**{{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE column_name IS NULL OR column_name = ''</nowiki>}}<ref>[http://stackoverflow.com/questions/17832906/how-to-check-if-field-is-null-or-empty-mysql How to check if field is null or empty mysql? - Stack Overflow]</ref>
* method2: 
** {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE column_name > ''</nowiki>}}
** {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) > 0</nowiki>}}
** {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) != 0</nowiki>}}
* method3: {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE column_name IS NULL</nowiki>}}
* method4: {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE column_name IS NOT NULL</nowiki>}}

==== VARCHAR or numeric ====
<table border="1" style="width: 100%">
  <tr style="background-color: #555555; color: #ffffff;">
    <td>data type of column</td>
    <td>possible column values</td>
    <td>method5: <br />find values within the range</td> 
    <td>method6: <br />find values out of the range, empty & NULL values</td>
  </tr>
  <tr>
    <td rowspan="4">{{kbd | key = VARCHAR}} or numeric</td>
    <td>values within the range ex:  min ≤ value ≤ max</td>
    <td>V</td> 
    <td></td>
  </tr>
  <tr>
    <td>values out of range</td>
    <td></td> 
    <td>V</td>
  </tr>
  <tr>
    <td>{{kbd | key = NULL}}</td>
    <td></td> 
    <td>V</td>
  </tr>
  <tr>
    <td>EMPTY ex: <nowiki>'' or space '  '</nowiki></td>
    <td></td> 
    <td>V</td>
  </tr>
</table>

* method5: {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE column_name BETWEEN min AND max</nowiki>}}
* method6: {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE (   (COALESCE(column_name, '') = '') OR (column_name NOT BETWEEN min AND max)   )</nowiki>}}

==== datetime and allows NULL value ====
possible column values
# {{kbd | key = {{Template:Today}}}}
# {{kbd | key = 00-00-00 00:00:00}}
# {{kbd | key = NULL}}