题解 | 电话号码格式校验-(不会正则表达式，只会like，%，_怎么办？)

电话号码格式校验

https://www.nowcoder.com/practice/2c2453e909c04c29a04c11e9d6b2c769

select id,name,phone_number
from contacts c
where  (c.phone_number  like '___-___-____'
 or length(c.phone_number)=10
 )
and (c.phone_number  like '1%'
or c.phone_number  like '2%'
or c.phone_number  like '3%'
or c.phone_number  like '4%'
or c.phone_number  like '5%'
or c.phone_number  like '6%'
or c.phone_number  like '7%'
or c.phone_number  like '8%'
or c.phone_number  like '9%'
)
order by id

也许你没有学过正则表达式，但是一定学过 name like '%dd_'这个简单的通配符，

%代表任意数量的字符

_代表一个字符

length函数获取长度

（正式代码还是推荐正则表达式的，如果在写题的时候可以强出解，反正sql没有时间复杂度要求）

全部评论

推荐最新楼层

clidhoose

南开大学测试工程师

并不行，如果仅是为了避免以0开头，完全可以使用NOT(c.phone_number LIKE '0%')的方式，并且你的代码并没有解决存在其他字符的问题，建议还是使用正则表达式。

3 回复分享

发布于 06-27 21:55 湖南

起床了的回笼觉觉主很想顺利毕业

郑州轻工业大学 Hadoop

not like "0%" 就行了

1 回复分享

发布于 07-23 11:14 上海

风吹柳语

西北大学数据分析师

select id, name, phone_number from contacts where phone_number not REGEXP '[abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ]' and substring(phone_number, 1, 1) <> "0" and ( ( phone_number like "%-%-%" and length(substring_index(phone_number, "-", -1)) = 4 and length(substring_index(phone_number, "-", 1)) = 3 and length(replace(phone_number, "-", "")) = 10 ) or ( length(phone_number) = 10 and phone_number not like "%-%-%" ) )

点赞回复分享

发布于 09-05 19:20 陕西

牛客683108877号

SELECT id, name,phone_number from contacts where ((phone_number like '__________'and length(phone_number) =10 and (substr(phone_number ,1,1) in (1,2,3,4,5,6,7,8,9) and substr(phone_number ,2,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,3,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,4,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,5,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,6,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,7,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,8,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,9,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,10,1) in (1,2,3,4,5,6,7,8,9,0) )) or (phone_number like '___-___-____' and length(phone_number) =12) and (substr(phone_number ,1,1) in (1,2,3,4,5,6,7,8,9) and substr(phone_number ,2,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,3,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,4,1) in (1,2,3,4,5,6,7,8,9,0,'-') and substr(phone_number ,5,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,6,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,7,1) in (1,2,3,4,5,6,7,8,9,0,'-') and substr(phone_number ,8,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,9,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,10,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,11,1) in (1,2,3,4,5,6,7,8,9,0) and substr(phone_number ,12,1) in (1,2,3,4,5,6,7,8,9,0) )) and phone_number not like '0%' order by id 这样子才对，你的无法真正限制住

点赞回复分享

发布于 08-06 19:32 广东

存几分期许

西南大学数据分析师

left(phone_number, 1)<>0

点赞回复分享

发布于 08-06 11:38 四川

伤心懒羊羊

湖南工商大学 Java

忍耐王

点赞回复分享

发布于 05-16 15:42 湖南