14,970
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
no edit summary
No edit summary |
|||
| Line 1: | Line 1: | ||
<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: <nowiki>'' or ' '</nowiki></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 | |||
== is null == | == is null == | ||