declare @Phone varchar(100);

select @Phone = '+44 (0) 1908 123 456';

select stuff(stuff(replace(@Phone, '(0) ', ''), 5, 0, '('), 10, 0, ')');

--===== Create and populate a test table.  This is NOT part of the solution
DECLARE @PhoneNumbers TABLE (Original VARCHAR(30))
 INSERT INTO @PhoneNumbers (Original)
 SELECT '+44 (0) 1908 123 456' UNION ALL
 SELECT '+44 (0) 121 430 4992' 

 SELECT Original,
        REPLACE(STUFF(PartialFormat,CHARINDEX(' ',PartialFormat),0,')'),'(',' (') AS Reformatted
   FROM (--==== Partially reformat the phone number by replacing the (0) and surrounding spaces
             -- with just a left parentheses...
         SELECT Original, REPLACE(Original, ' (0) ','(') AS PartialFormat FROM @PhoneNumbers
        ) d