15,076
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
→Verify if value is number in MySQL
| (2 intermediate revisions by the same user not shown) | |||
| Line 315: | Line 315: | ||
* Check if a value is integer e.g. 1234567 | * Check if a value is integer e.g. 1234567 | ||
** 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><ref>[https://stackoverflow.com/questions/75704/how-do-i-check-to-see-if-a-value-is-an-integer-in-mysql How do I check to see if a value is an integer in MySQL? - Stack Overflow]</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]+$'}}<ref>[http://stackoverflow.com/questions/14343767/mysql-regexp-with-and-numbers-only regex - Mysql REGEXP with . and numbers only - Stack Overflow]</ref><ref>[https://stackoverflow.com/questions/75704/how-do-i-check-to-see-if-a-value-is-an-integer-in-mysql How do I check to see if a value is an integer in MySQL? - Stack Overflow]</ref> | ||
* Find the records which the value of `my_column` is not exactly 8 digits {{code | code = SELECT * FROM my_table WHERE LENGTH(my_column) != 8 OR my_column NOT REGEXP '^[0-9]{8}$'}} | |||
** The `LENGTH()` function checks if the string length is not 8 characters | |||
** The `REGEXP '^[0-9]{8}$'` pattern validates that the value contains exactly 8 digits from start (^) to end ($) | |||
** Using both conditions ensures catching values with correct length but non-numeric characters, as well as incorrect lengths | |||
* Check if a value is integer which may contains comma and dot symbols e.g. 1,234.567 or 3.414 | * Check if a value is integer which may contains comma and dot symbols e.g. 1,234.567 or 3.414 | ||
| Line 366: | Line 371: | ||
Abnormal values | Abnormal values | ||
* {{code | code = 1970-01-01 08:00:00}} (converted time formatted value from {{code | code =August 3, 2017}}) caused by the string contains special | * {{code | code = 1970-01-01 08:00:00}} (converted time formatted value from {{code | code =August 3, 2017}}) caused by the string contains special characters e.g. [https://en.wikipedia.org/wiki/Left-to-right_mark left-to-right mark (LRM) ] | ||
Check if the date valid | Check if the date valid | ||
| Line 467: | Line 472: | ||
== References == | == References == | ||
<references/> | <references/> | ||
{{Template:Data factory flow}} | |||
[[Category:Spreadsheet]] [[Category:Excel]] | [[Category:Spreadsheet]] [[Category:Excel]] | ||