Formatting Phone Numbers in Non-US format

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.

>Solution :

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