在Sql Server中设置电话号码格式

Question

我有一个电话号码如下：

Telephone No
03-58164661
03-5816-7791
03-6433-4661
03-45673661
22-345-1234
564-33-6789

我只需要用"-"分隔八位数

03-5816-4661
03-5816-7791
03-6433-4661
03-4567-3661
22-345-1234
564-33-6789

Answer 1

您可以尝试此操作，STUFF将为您添加连字符，CASE语句确保只有没有连字符的才受影响

Declare @s varchar(50) = '03-5816-4661'
Select 
CASE WHEN RIGHT(@s,5) !='-' then 
Stuff(@s, Len(@s)-3, 0, '-')
else @S
end as [Tele]

Answer 2

JobTout,

对你的脚本做一些小改动对我来说很有效。

Declare @s varchar(50) = '03-58164661'
Select 
CASE WHEN LEN(@s) - LEN(REPLACE(@s,'-','')) = 1 then -- AND RIGHT(@s,5) !='-' then 
Stuff(@s, Len(@s)-3, 0, '-')
else @S
end as [Tele]

Answer 3

假设所有数字的长度与示例中显示的长度相同。

declare @num varchar(50)='0358164661'
select reverse(stuff(reverse(stuff(@num,3,case when CHARINDEX('-',@num,1)<>3 then 0 
else 1 end ,'-')),5,case when CHARINDEX('-',@num,4)<>8 then 0 else 1 end,'-'))

更新的答案适用于所有提到的场景

select case when CHARINDEX('-',SUBSTRING (@num,1,5),1)<>0 then SUBSTRING 
(@num,1,5)  else stuff(SUBSTRING (@num,1,5),3,0,'-') end
+reverse(case when CHARINDEX('-',SUBSTRING (reverse(@num),1,len(@num)-5),1)<>0 
then SUBSTRING (reverse(@num),1,len(@num)-5)  
else stuff(SUBSTRING (reverse(@num),1,len(@num)-5),5,0,'-') end)