Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→Numeric: Check if the column value is integer
m (Text replacement - "Category:Text file processing" to "Category:String manipulation") |
(→Numeric: Check if the column value is integer) |
||
| Line 258: | Line 258: | ||
* If the data was imported from Excel, you should notice the 15 digit precision issue. | * If the data was imported from Excel, you should notice the 15 digit precision issue. | ||
=== | === Check if the column value is integer === | ||
MySQL: | |||
* Find the records which the value of `my_column` is numeric values entirely {{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 the records which the value of `my_column` is '''NOT''' numeric values entirely {{code | code = SELECT * FROM `my_table` WHERE `my_column` NOT REGEXP '^[0-9]+$'}} | |||
If the digit of number is known, the SQL syntax could be more specific | |||
* The {{kbd | key=tax_id}} column is 8 digits only. Find the well-formatted {{kbd | key=tax_id}} records by using {{code | code = SELECT * FROM `tax_id` WHERE `tax_id` REGEXP '^[0-9]{8}$'}} | |||
=== Check if the column value is numeric === | |||
List of the possible abnormal values: | List of the possible abnormal values: | ||
* All numeric values are odd or even if the data were generated by user naturally. | * All numeric values are odd or even if the data were generated by user naturally. | ||
| Line 266: | Line 275: | ||
MySQL: | MySQL: | ||
* Find the records which the value of `my_column` is numeric values entirely {{code | code = SELECT * FROM `my_table` WHERE `my_column` REGEXP '^[0-9]+$'}}<ref>[ | * Find the records which the value of `my_column` is numeric values entirely {{code | code = SELECT * FROM `my_table` WHERE `my_column` REGEXP '^[0-9]+\.?[0-9]+$'}}<ref>[https://community.denodo.com/answers/question/details?questionId=9060g000000XelhAAC&title=How+to+identify+if+values+in+a+column+is+numeric+%28+Function+similar+to+Isnumeric+is+SQL%29 How to identify if values in a column is numeric ( Function similar to Isnumeric is SQL)]</ref> | ||
Excel & [https://www.google.com/sheets/about/ Google Sheets]: | Excel & [https://www.google.com/sheets/about/ Google Sheets]: | ||