题解 | 电话号码格式校验

select id,name,phone_number
from contacts
where (phone_number not like '0%' 
    and LENGTH(phone_number) = 10
    and phone_number not REGEXP '[^0-9]'
    and phone_number not like '%-%'
    )
    or 
    (phone_number like '___-___-____'
    and phone_number not like '0%'
    and LENGTH(REPLACE(phone_number, '-', '')) = 10
    and REPLACE(phone_number, '-', '') not REGEXP '[^0-9]');

要注意MYSQL中不支持[^0-9]需要用正则化regexp

not REGEXP '[^0-9]'