SQL Server format phone number

-- 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