Data cleaning: Difference between revisions

Data cleaning (edit)

1,276 bytes added , 12 March 2015

no edit summary

14,970

edits

@@ 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 ==