Editing Data cleaning (section)

=== check if field value was not fulfilled: NULL, empty value ===
{{exclaim}} NOT include those data which its field value  fulfilled with default value automatically ([http://sqlfiddle.com/#!2/9b01e/3/0 demo on sqlfiddle])

# {{Gd}} quick solution: find records with {{kbd | key = NULL}} value OR empty, space value
#* MySQL solution: {{kbd | key = SELECT * FROM table_name WHERE column_name IS NULL OR LENGTH(TRIM( column_name )) = 0;}} 
# 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;}} 
#* EXCEL: {{kbd | key =<nowiki>=EXACT(A2, "NULL")</nowiki>}}
# find records with empty value: (not contains {{kbd | key = NULL}} value)
#* MySQL: {{kbd | key = SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) = 0;}} {{Exclaim}} SQL query {{kbd | key =SELECT * FROM table_name WHERE column_name IS NOT NULL}} includes empty value 
#* MS SQL Server: {{kbd | key = SELECT * FROM table_name WHERE LEN( LTRIM(RTRIM(column_name)) ) = 0;}} <ref>[http://www.1keydata.com/tw/sql/sql-trim.html SQL TRIM 函數 - 1Keydata SQL 語法教學]</ref>
#  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
#* trivial approach : EXCEL: {{kbd | key =<nowiki>=IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)-YEAR(NOW())>100, "", A2))</nowiki>}}  {{exclaim}} this formula could not handle empty value because it return 0. If I change the format of cell to time format, 0 will become 1900/1/0.
# Using PHP 
#* [http://php.net/manual/en/function.empty.php empty()] function to find 0, null, false, empty string, empty array values.
#* <code>if(empty($var) && $var !== 0 && $var !== "0"){ .. } </code> to find null, false, empty string, empty array values BUT not 0.
# check if field value was NULL & not equal to some value
#* MySQL: [http://stackoverflow.com/questions/17097890/mysql-select-on-fields-containing-null-values MySql select on fields containing null values - Stack Overflow]