Data cleaning: Difference between revisions
Jump to navigation
Jump to search
m (→is null) |
|||
| Line 5: | Line 5: | ||
* Google spreadsheet / Excel: | * Google spreadsheet / Excel: | ||
** [https://support.google.com/drive/answer/3093348 ISERR(value)] " value - The value to be verified as an error type other than #N/A." ex: {{kbd | key = #NULL!}} | ** [https://support.google.com/drive/answer/3093348 ISERR(value)] " value - The value to be verified as an error type other than #N/A." ex: {{kbd | key = #NULL!}} | ||
** If the cell value is exactly {{kbd | key = NULL}} not {{kbd | key = #NULL!}}, You may use {{kbd | key = <nowiki>EXACT(value, "NULL")</nowiki>}} | ** If the cell value is exactly {{kbd | key = NULL}} not {{kbd | key = #NULL!}}, You may use {{kbd | key = <nowiki>COUNTIF(value, "NULL")</nowiki>}} or {{kbd | key = <nowiki>EXACT(value, "NULL")</nowiki>}} | ||
* MySQL SQL syntax: {{kbd | key = SELECT * FROM table WHERE column IS NULL;}}<ref>[http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html MySQL :: MySQL 5.0 Reference Manual :: 3.3.4.6 Working with NULL Values]</ref> [http://sqlfiddle.com/#!2/ec1152/1/0 demo] | * MySQL SQL syntax: {{kbd | key = SELECT * FROM table WHERE column IS NULL;}}<ref>[http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html MySQL :: MySQL 5.0 Reference Manual :: 3.3.4.6 Working with NULL Values]</ref> [http://sqlfiddle.com/#!2/ec1152/1/0 demo] | ||
Revision as of 00:34, 21 May 2014
is null
Finds whether a variable is NULL
- PHP is_null
- Google spreadsheet / Excel:
- ISERR(value) " value - The value to be verified as an error type other than #N/A." ex: #NULL!
- If the cell value is exactly NULL not #NULL!, You may use COUNTIF(value, "NULL") or EXACT(value, "NULL")
- MySQL SQL syntax: SELECT * FROM table WHERE column IS NULL;[1] demo
Finds whether a variable is NOT NULL
- MySQL SQL syntax: SELECT * FROM table WHERE column IS NOT NULL; demo
check if field value was not fulfilled: NULL, empty value
NOT include those data which its field value fulfilled with default value automatically
- NULL value: SELECT * FROM table_name WHERE column_name IS NULL;
- empty value: SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) = 0; SQL query with the condition SELECT * FROM table_name WHERE column_name IS NOT NULL includes empty value
check if field contains value
- MySQL: SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) != 0; demo
outlier
(left blank intentionally)
data handling
remove first, last or certain characters from text
- Excel: using RIGHT[2] + LEN[3] functions [4]
- Excel: if the text length will be removed was fixed, you may try to use REPLACE[5] + LEN functions (demo)
Data modeling: Data type