Formatting Phone Numbers in Non-US format

Question

I have the following code that I'm trying to refactor to format a phone number field in the format I need it in:

STUFF(STUFF(STUFF(REPLACE('02 212345678','02 2','02 '), 7, 0, ' '), 3, 0, ') '), 1, 0, '(') 

It returns data currently as this:

(02)  123 45678

where I need it in this format

(02) 1234 5678

The problem is the extra space after the closing bracket and having 4 numbers either side.

Answer 1

Based on your example, does the following work for you?

with sampledata as (select '02 212345678' num)

select Concat(Stuff('() ',2,0,Left(num,2)), Stuff(Right(num,8),5,0,' '))
from sampledata

Answer 2

You existing code works, just need to change the 7 to an 8, but I far prefer to keep my formatting and data seperated. The code is waaaaaay easier to read and modify:

DECLARE @PhoneNumber varchar(20) = '02 212345678';

SELECT FORMATMESSAGE('(%s) %s %s', LEFT(@PhoneNumber, 2), SUBSTRING(@PhoneNumber, 4, 4), SUBSTRING(@PhoneNumber, 8, 4) );