14,962
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
m
no edit summary
mNo edit summary |
|||
| Line 282: | Line 282: | ||
*** You need to check the {{code | code = SELECT CURRENT_TIMESTAMP);}} if correct or not before you delete the abnormal data (timezone issue) | *** You need to check the {{code | code = SELECT CURRENT_TIMESTAMP);}} if correct or not before you delete the abnormal data (timezone issue) | ||
== duplicate data == | == duplicate data == | ||
| Line 342: | Line 333: | ||
If the data values were generated by different users, the unique number of data values should be larger than ____ | If the data values were generated by different users, the unique number of data values should be larger than ____ | ||
== | == 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 length of text was fixed after removed, 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 length of text was fixed after removed, 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]) | ||
=== Remove leading and trailing spaces from text === | |||
* PHP: [http://php.net/manual/en/function.trim.php trim function] | |||
* Excel: [https://exceljet.net/formula/remove-leading-and-trailing-spaces-from-text Excel formula: Remove leading and trailing spaces from text | Exceljet] | |||
* MySQL: Using [http://www.w3resource.com/mysql/string-functions/mysql-trim-function.php TRIM() function] & [http://www.w3resource.com/mysql/string-functions/mysql-length-function.php LENGTH() function ] | |||
<pre> | |||
UPDATE `table` | |||
SET `column` = TRIM( `column` ) | |||
WHERE LENGTH(TRIM( `column` )) != LENGTH( `column` ); | |||
</pre> | |||
== related pages == | == related pages == | ||