This way replaces the non existing IS_NUMERIC() function in MySQL:

SELECT unit FROM testTable WHERE unit NOT REGEXP '^[+-]?[0-9]*([0-9]\\.|[0-9]|\\.[0-9])[0-9]*(e[+-]?[0-9]+)?$'