13,468
edits
Data cleaning: Difference between revisions
Jump to navigation
Jump to search
add verify the field value
(add verify the field value) |
|||
| Line 27: | Line 27: | ||
== check if field contains value == | == check if field contains value == | ||
# 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] | ||
== verify the field value == | |||
email: | |||
* EXCEL: {{kbd | key =<nowiki>=IF(ISERR(FIND("@", A2, 1)), FALSE, TRUE)</nowiki>}} only check the field if contains @ symbol or not | |||
** result: (1) normal condition: return TRUE; (2) exceptional condition: return '''FALSE''' if @ symbol was not found | |||
* EXCEL: {{kbd | key =<nowiki>=FIND("@", A2, 2)</nowiki>}} only check the field if contains @ symbol or not | |||
** syntax: <nowiki>FIND(find_text, with_text, [start_num])</nowiki> the start_num is 2 because the position of @ symbol should be larger than 1 (position of first char is 1) | |||
** result: (1) normal condition: return the number larger than 1; (2) exceptional condition: return '''#VALUE!''' if @ symbol was not found | |||
* PHP: [http://www.w3schools.com/php/filter_validate_email.asp PHP FILTER_VALIDATE_EMAIL Filter] | |||
** "Returns the filtered data, or '''FALSE''' if the filter fails." quoted from [http://php.net/manual/en/function.filter-var.php PHP.net] | |||
== outlier == | == outlier == | ||