Data cleaning

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; 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

(demo on sqlfiddle)

1. NULL value:
   • solution: SELECT * FROM table_name WHERE column_name IS NULL;
   • EXCEL: =EXACT(A2, "NULL")
2. empty value:
   • solution1: SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) = 0;
   • solution2: SQL query with the condition SELECT * FROM table_name WHERE column_name IS NOT NULL includes NULL & empty value
3. 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: =IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)<1914, "", A2)) (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: =IF(ISERR(YEAR(A2)), "", IF(YEAR(A2)-YEAR(NOW())>100, "", A2)) 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.

check if field contains value

1. 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 + LEN functions
• Excel: if the text length will be removed was fixed, you may try to use REPLACE + LEN functions (demo)

related pages

Data modeling: Data type