Hi @srk ,
Please try the following solution.
It will work starting from SQL Server 2017 onwards.
SQL
-- DDL and sample data population, start
DECLARE @tbl TABLE (ObjectId int PRIMARY KEY, Number nvarchar(20));
INSERT INTO @tbl (ObjectId, Number) VALUES
(1001,'447875288997'),
(1002,'+4412674498589'),
(1003,'4408690446873'),
(1004,'07875288996'),
(1005,'086678764889'),
(1006,'086678744889');
-- DDL and sample data population, end
DECLARE @countryCode VARCHAR(3) = '44';
SELECT *
, Result = TRIM('+' FROM IIF(pos IN (1,2),STUFF(Number, pos, LEN(@countryCode), 0),Number))
FROM @tbl
CROSS APPLY (SELECT CHARINDEX(@countryCode, Number)) AS t(pos);
Output
+----------+----------------+-----+---------------+
| ObjectId | Number | pos | Result |
+----------+----------------+-----+---------------+
| 1001 | 447875288997 | 1 | 07875288997 |
| 1002 | +4412674498589 | 2 | 012674498589 |
| 1003 | 4408690446873 | 1 | 008690446873 |
| 1004 | 07875288996 | 0 | 07875288996 |
| 1005 | 086678764889 | 0 | 086678764889 |
| 1006 | 086678744889 | 8 | 086678744889 |
+----------+----------------+-----+---------------+