mysqlmysql-workbench

Query to fetch records having combination of integer and string

I have to find junk auditorium having combination of int and string, but by using LIKE operator I am even getting only strings also (which is valid) in my result.

8 XP
Reserved 20
15 Reserved
Drive In 91.9 FM
Apple Xtreme
Atmos Reserved

select 
    distinct auditorium, 
    circuit_name 
from table_name
where 
    date >= current_date() 
    and 
    auditorium like "% %" 
order by 
    circuit_name;

Should be only getting below as output

8 XP
Reserved 20
15 Reserved
Drive In 91.9 FM
Solution

  • You need to use a regular expression to distinguish letters and numbers. Your LIKE patter matches any value containing a space.

    auditorium REGEXP '[a-z].*[0-9]|[0-9].*[a-z]'

    The first alternative matches a letter followed later by a digit, the second matches them in the other order.