sql - time length in VARCHAR mysql -

i have table time length type stored varchar, , try select longer 2 , half hours:

mysql> select * `1970’s movies`.movies length; +----------------------------------+------+---------------+-------------+------------------+ | title                            | year | 5-star rating | length      | studio           | +----------------------------------+------+---------------+-------------+------------------+ | star wars                        | 1977 |             5 | 2 hr 1 min  | 20th century fox | | national lampoon’s animal house  | 1978 |             3 | 1 hr 49 min | universal        | | jaws                             | 1975 |             4 | 2 hr 5 min  | universal        | | exorcist                     | 1973 |             4 | 2 hr 2 min  | warner bros.     | | grease                           | 1978 |             2 | 1 hr 50 min | paramount        | | godfather                    | 1972 |             5 | 2 hr 55 min | paramount        | | towering inferno             | 1974 |             3 | 2 hr 45 min | 20th century fox | | black hole                   | 1979 |             1 | 1 hr 38 min | disney           | | superman                         | 1978 |             2 | 2 hr 23 min | warner bros.     | | godfather part ii            | 1974 |             5 | 3 hr 20 min | paramount        | | love story                       | 1970 |             1 | 1 hr 40 min | paramount        | +----------------------------------+------+---------------+-------------+------------------+ 

mysql permissive when converting numbers strings, can make work you. example:

select convert('2 hr 1 min', unsigned) --> returns 2 

that give hours. minutes, 3 characters after 'hr':

select substr(length, locate('hr', length) + 3) --> returns '1 min' star wars 

if apply convert against '1 min', mysql return number 1.

to show movie title hours , minutes, this:

select   title,   convert(length, unsigned) hours,   convert(substr(length, locate('hr', length) + 3), unsigned) minutes movies 

to show movies more 2 , half hours:

select * movies convert(length, unsigned) >= 2   , convert(substr(length, locate('hr', length) + 3), unsigned) >= 30