我有一个数据库,里面的电话号码看起来像这样:
0041 123423423
0042 783728492
0043 832792922
0044 738922222
但也有一些数字看起来像这样:
00432 7462893-99
0042 873946278-100
0041 837227-1234
现在我想将所有电话号码转换为以下格式:
"00[2 digits] [4 digits] [5 digits] [3 digits]"
显然,并非所有电话号码都有相同数量的数字,但仍然应该格式化。这应该通过SQL完成(我认为我没有其他可能性)。我正在使用MySQL。
电话号码示例:
Unformatted: 004912345
Formatted: 0049 1234 5
谢谢!
这
应该对您有所帮助..最后一列是格式的结果
SELECT
@number := REPLACE(number,' ','') as minusspace,
@number := REPLACE(@number,'-','') as minusdash,
@first := LEFT(@number,4) as first,
@second := RIGHT(LEFT(@number,8),4) as second,
@third := RIGHT(LEFT(@number,13),LEAST(LENGTH(@number)-8,5)) as third,
@fourth := RIGHT(LEFT(@number,16),LEAST(LENGTH(@number)-13,3)) as fourth,
TRIM(CONCAT(@first,' ',@second,' ',@third,' ',@fourth)) as formatted_phone
FROM phone
SQLfiddle