14,970
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→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">VARCHAR</td> | |||
<td>fulfilled value ex:123</td> | |||
<td></td> | |||
<td>V</td> | |||
<td>V</td> | |||
<td></td> | |||
</tr> | |||
<tr> | |||
<td>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</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 == | ||