13,314
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→quick table
No edit summary |
|||
| Line 7: | Line 7: | ||
<td>method1: <br />find not fulfilled or empty values</td> | <td>method1: <br />find not fulfilled or empty values</td> | ||
<td>method2: <br />find fulfilled and non-empty values</td> | <td>method2: <br />find fulfilled and non-empty values</td> | ||
<td>method3: <br />find | <td>method3: <br />find NULL values</td> | ||
<td>method4: <br />find NULL values</td> | <td>method4: <br />find not NULL values</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
| Line 15: | Line 15: | ||
<td></td> | <td></td> | ||
<td>V</td> | <td>V</td> | ||
<td></td> | |||
<td>V</td> | <td>V</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
| Line 22: | Line 22: | ||
<td>V</td> | <td>V</td> | ||
<td></td> | <td></td> | ||
<td></td> | <td>V</td> | ||
<td> | <td></td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
| Line 29: | Line 29: | ||
<td></td> | <td></td> | ||
<td>V</td> | <td>V</td> | ||
<td></td> | |||
<td>V</td> | <td>V</td> | ||
</tr> | </tr> | ||
<tr> | <tr> | ||
| Line 42: | Line 42: | ||
* method1: SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) = 0 | |||
* 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: | * method2: | ||
** SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) > 0 | ** {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE column_name > ''</nowiki>}} | ||
** SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) != 0 | ** {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) > 0</nowiki>}} | ||
* method3: SELECT * FROM `my_table` WHERE column_name IS | ** {{kbd | key = <nowiki>SELECT * FROM `my_table` WHERE LENGTH(TRIM( column_name )) != 0</nowiki>}} | ||
* method4: SELECT * FROM `my_table` WHERE column_name IS NULL | * 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>}} | |||
== is null == | == is null == | ||