SQL Server format phone number

原创

emanlee 2023-11-16 14:42:55 ©著作权

©著作权归作者所有：来自51CTO博客作者emanlee的原创作品，请联系作者获取转载授权，否则将追究法律责任

-- Try:

/* format (123) 456-7 to 123-4567 */ 
select   SUBSTRING('(123) 456-7', 2, 3) + '-' +  SUBSTRING('(123) 456-7', 7, 3) +  SUBSTRING('(123) 456-7', 11, 1) /* format 123-456-7890 to (123) 456-7890 */   
select '(' + SUBSTRING('123-456-7890', 1, 3) + ') ' +  SUBSTRING('123-456-7890', 5, 3) + '-' +  SUBSTRING('123-456-7890', 9, 4) /* format 1234567 to 123-4567 */   
select   SUBSTRING('1234567', 1, 3) + '-' +  SUBSTRING('1234567', 4, 4) /* format  1234567890  to  (123) 456-7890 */   
 select '(' + SUBSTRING('1234567890', 1, 3) + ') ' +  SUBSTRING('1234567890', 4, 3) + '-' +  SUBSTRING('1234567890', 7, 4)

-- Format:

/* format (123) 456-7 to 123-4567 */   
update tableAAA set phone= SUBSTRING(phone, 2, 3) + '-' +  SUBSTRING(phone, 7, 3) +  SUBSTRING(phone, 11, 1)      
where len(phone)=11 and CHARINDEX('(',phone)=1 and CHARINDEX(')',phone)=5  and CHARINDEX('-',phone)=10 /* format 123-456-7890 to (123) 456-7890 */   
update tableAAA set phone= '(' + SUBSTRING(phone, 1, 3) + ') ' +  SUBSTRING(phone, 5, 3) + '-' +  SUBSTRING(phone, 9, 4)  
where  len(phone)=12 and CHARINDEX('-',phone)=4 and substring(phone,8,1)='-' and charindex('(',phone)= 0 and charindex(')',phone)= 0 /* format 1234567 to 123-4567 */   
update tableAAA set phone=SUBSTRING(phone, 1, 3) + '-' +  SUBSTRING(phone, 4, 4)     
where  len(phone)=7 and charindex('-',phone)= 0 and charindex('(',phone)= 0 and charindex(')',phone)= 0 /* format  1234567890  to  (123) 456-7890 */   
update tableAAA set phone= '(' + SUBSTRING(phone, 1, 3) + ') ' +  SUBSTRING(phone, 4, 3) + '-' +  SUBSTRING(phone, 7, 4)     
where  len(phone)=10 and charindex('-',phone)= 0 and charindex('(',phone)= 0 and charindex(')',phone)= 0