I have a table that was loaded with just over 5 million rows of data from a local file. The table was created, and the data type was assigned to each column based on the spec sheet that came with the data. The last column in the table is called status_code, and it’s a varchar(10). The data loaded into this column is ‘Active’ or ‘Inactive’ or empty. If I run SELECT COUNT(status_code) WHERE status_code = ‘active,’ it will only count the rows where I have physically entered the status_code. it will not recognize the rows that were loaded from the file. For Example, I run SELECT COUNT(status_code), I get back 0. If I then go into the table and manually delete the data in the column and reenter and save the table, the SELECT COUNT(status_code) will return the number that is equal to the number of rows I manuannly updated. How can I correct the data so that all the rows in that column are properly recognized?
Maybe there are trailing spaces in the values ('active ’ instead of ‘active’)? If so, you can use TRIM() to correct that.
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_trim