Data cleaning: Difference between revisions
Jump to navigation
Jump to search
No edit summary |
|||
| Line 20: | Line 20: | ||
# MySQL: {{kbd | key = SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) != 0;}} [http://sqlfiddle.com/#!2/f491c/1/0 demo] | # MySQL: {{kbd | key = SELECT * FROM table_name WHERE LENGTH(TRIM( column_name )) != 0;}} [http://sqlfiddle.com/#!2/f491c/1/0 demo] | ||
== remove first, last or certain characters from text == | == outlier == | ||
(left blank intentionally) | |||
== data handling == | |||
=== remove first, last or certain characters from text === | |||
* Excel: using {{kbd | key=RIGHT}}<ref>[http://office.microsoft.com/zh-tw/excel-help/HP010342854.aspx RIGHT、RIGHTB 函數 - Excel - Office.com]</ref> + {{kbd | key=LEN}}<ref>[http://office.microsoft.com/zh-tw/excel-help/HP010342650.aspx LEN、LENB 函數 - Excel - Office.com]</ref> functions <ref>[http://www.extendoffice.com/documents/excel/560-excel-remove-character-from-string.html How to remove first, last or certain characters from text in Excel?]</ref> | * Excel: using {{kbd | key=RIGHT}}<ref>[http://office.microsoft.com/zh-tw/excel-help/HP010342854.aspx RIGHT、RIGHTB 函數 - Excel - Office.com]</ref> + {{kbd | key=LEN}}<ref>[http://office.microsoft.com/zh-tw/excel-help/HP010342650.aspx LEN、LENB 函數 - Excel - Office.com]</ref> functions <ref>[http://www.extendoffice.com/documents/excel/560-excel-remove-character-from-string.html How to remove first, last or certain characters from text in Excel?]</ref> | ||
* Excel: if the text length will be removed was fixed, you may try to use {{kbd | key=REPLACE}}<ref>[http://office.microsoft.com/zh-tw/excel-help/HP010342844.aspx REPLACE、REPLACEB 函數 - Excel - Office.com]</ref> + {{kbd | key=LEN}} functions ([http://bit.ly/1i2tsgk demo]) | * Excel: if the text length will be removed was fixed, you may try to use {{kbd | key=REPLACE}}<ref>[http://office.microsoft.com/zh-tw/excel-help/HP010342844.aspx REPLACE、REPLACEB 函數 - Excel - Office.com]</ref> + {{kbd | key=LEN}} functions ([http://bit.ly/1i2tsgk demo]) | ||
Revision as of 14:51, 15 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
- 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)