Data cleaning: Difference between revisions
Jump to navigation
Jump to search
| Line 13: | Line 13: | ||
== check if field value was not fulfilled: NULL, empty value == | == check if field value was not fulfilled: NULL, empty value == | ||
# {{kbd | key = NULL}} value: {{kbd | key = SELECT * FROM table_name WHERE column_name IS NULL;}} | ([http://sqlfiddle.com/#!2/9b01e/3/0 demo on sqlfiddle]) | ||
# {{kbd | key = NULL}} value: {{kbd | key = SELECT * FROM table_name WHERE column_name IS NULL;}} | |||
# empty value: {{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 | # empty value: {{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 | ||
Revision as of 18:28, 4 March 2014
is null
Finds whether a variable is NULL
- PHP is_null
- Google spreadsheet:
- 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 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
- 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
(left blank intentionally)
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