13,468
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→check if field value was not fulfilled: NULL, empty value
| Line 15: | Line 15: | ||
([http://sqlfiddle.com/#!2/9b01e/3/0 demo on sqlfiddle]) | ([http://sqlfiddle.com/#!2/9b01e/3/0 demo on sqlfiddle]) | ||
# {{kbd | key = NULL}} value: (note: not {{kbd | key = #NULL!}}) | # find records with {{kbd | key = NULL}} value: (note: not {{kbd | key = #NULL!}}) | ||
#* MySQL solution: {{kbd | key = SELECT * FROM table_name WHERE column_name IS NULL;}} | #* MySQL solution: {{kbd | key = SELECT * FROM table_name WHERE column_name IS NULL;}} | ||
#* EXCEL: {{kbd | key =<nowiki>=EXACT(A2, "NULL")</nowiki>}} | #* EXCEL: {{kbd | key =<nowiki>=EXACT(A2, "NULL")</nowiki>}} | ||
# empty value: | # empty value: | ||
#* | #* find records with empty value: | ||
#** MySQL: {{kbd | key = SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) = 0;}} {{Exclaim}} SQL query with the condition {{kbd | key =SELECT * FROM table_name WHERE column_name IS NOT NULL}} includes empty value | |||
#* find records without NULL or empty value: | |||
#** MySQL: {{kbd | key =<nowiki>SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) != 0;</nowiki>}} | |||
# Excel starting date: 1900/1/0 (converted time formatted value from 0), 1900/1/1 (converted time formatted value from 1), 1900/1/2 ... | # Excel starting date: 1900/1/0 (converted time formatted value from 0), 1900/1/1 (converted time formatted value from 1), 1900/1/2 ... | ||
#* solution: step1: Replace the year > 100 from this year with empty value at EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)<1914, "", A2))</nowiki>}} (this formula also handle empty value and non well-formatted column value ex: 0000-12-31 ) ; step2: change the format of cell to time format | #* solution: step1: Replace the year > 100 from this year with empty value at EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)<1914, "", A2))</nowiki>}} (this formula also handle empty value and non well-formatted column value ex: 0000-12-31 ) ; step2: change the format of cell to time format | ||