14,962
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→Numeric only: +Google Sheets
(→Numeric only: +Google Sheets) |
|||
| Line 257: | Line 257: | ||
=== Numeric only === | === Numeric only === | ||
PHP: | |||
* [http://php.net/manual/en/function.is-numeric.php is_numeric] | |||
MySQL: | |||
* find numeric values {{code | code = SELECT * FROM `my_table` WHERE `my_column` REGEXP '^[0-9]+$'}}<ref>[http://stackoverflow.com/questions/14343767/mysql-regexp-with-and-numbers-only regex - Mysql REGEXP with . and numbers only - Stack Overflow]</ref> | |||
* find NOT numeric values {{code | code = SELECT * FROM `my_table` WHERE `my_column` NOT REGEXP '^[0-9]+$'}} | |||
Excel: | |||
* [http://www.techonthenet.com/excel/formulas/isnumber.php ISNUMBER Function] | |||
[https://www.google.com/sheets/about/ Google Sheets] | |||
* Using [https://support.google.com/docs/answer/3098292?hl=zh-Hant REGEXMATCH] & [https://support.google.com/docs/answer/3093592?hl=zh-Hant CONCAT]<ref>[https://errerrors.blogspot.tw/2015/08/google.html GOOGLE 試算表: 數字轉成文字]</ref> functions: {{Kbd | key = <nowiki>=IF(REGEXMATCH(CONCAT("", A1), "^\d+$"), 1, "")</nowiki>}} If A1 cell is numeric will return 1, else return empty. If A1 cell contains [https://en.wikipedia.org/wiki/Scientific_notation Scientific notation] will return empty. | |||
<pre> | |||
test data: | |||
1.36184E+14 | |||
20740199601 | |||
346183773390240 | |||
</pre> | |||
=== Abnormal values of time data === | === Abnormal values of time data === | ||