具体实现步骤:
case when trim(mobile) regexp "^1([3][4-9]|[4][7]|[5][0-27-9]|[8][2-478])[0-9]{8}$" then '移动'
when trim(mobile) regexp "^1([3][0-2]|[4][5]|[5][5-6]|[7][6]|[8][5-6])[0-9]{8}$" then '联通'
when trim(mobile) regexp "^1(3[3]|5[3]|7[37]|8[019])[0-9]{8}$" then '电信'
when trim(mobile) not regexp '^1[0-9]{10}$' and length(trim(mobile))>=1 then '非手机号码'
when (trim(mobile) is null or length(trim(mobile))<1) then '无号码'
else '其他运营商'
end as telecoms