Data cleaning: Difference between revisions

Jump to navigation Jump to search

Data cleaning (edit)

Revision as of 11:18, 12 March 2015

1,259 bytes removed ,  12 March 2015
m
→‎check if field value was not fulfilled: NULL, empty value
Line 27: Line 27:
#* trivial approach : EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)-YEAR(NOW())>100, "", A2))</nowiki>}}  {{exclaim}} this formula could not handle empty value because it return 0. If I change the format of cell to time format, 0 will become 1900/1/0.
#* trivial approach : EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)-YEAR(NOW())>100, "", A2))</nowiki>}}  {{exclaim}} this formula could not handle empty value because it return 0. If I change the format of cell to time format, 0 will become 1900/1/0.
# Using PHP [http://php.net/manual/en/function.empty.php empty()] function to find 0, null, false, empty string, empty array values.
# Using PHP [http://php.net/manual/en/function.empty.php empty()] function to find 0, null, false, empty string, empty array values.
<table border="1" style="width: 100%">
  <tr style="background-color: #555555; color: #ffffff;">
    <td>data type</td>
    <td>allowed 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 not NULL values</td>
    <td>method4: <br />find NULL values</td>
  </tr>
  <tr>
    <td rowspan="4">{{kbd | key = VARCHAR}} and allows {{kbd | key = NULL}}</td>
    <td>fulfilled value ex:123</td>
    <td></td>
    <td>V</td>
    <td>V</td>
    <td></td>
  </tr>
  <tr>
    <td>{{kbd | key = NULL}}</td>
    <td>V</td>
    <td></td>
    <td></td>
    <td>V</td>
  </tr>
  <tr>
    <td>0</td>
    <td></td>
    <td>V</td>
    <td>V</td>
    <td></td>
  </tr>
  <tr>
    <td>EMPTY ex: '' or '  '</td>
    <td>V</td>
    <td></td>
    <td></td>
    <td></td>
  </tr>
</table>
* method1: SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) = 0 OR column_name IS NULL
* method2:
** SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) > 0
** SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) != 0
* method3: SELECT * FROM `my_table` WHERE column_name IS NOT NULL
* method4: SELECT * FROM `my_table` WHERE column_name IS NULL


== check if field value was fulfilled ==
== check if field value was fulfilled ==
Retrieved from "https://wiki.planetoid.info/index.php/Special:MobileDiff/14736"

Navigation menu