Replace leading 44 with 0 for phone numbers

srk 41 Reputation points
2022-03-21T22:02:17.55+00:00

Hi there,
I would like to replace leading 44 or 44+ with zero for phone numbers. Can you please suggest me how to do this?

create table phonenumber (ObjectId int , Number nvarchar(20))

insert into phonenumber 
values (1001,'447875288997'),
       (1002,'+4412674498589'),
       (1003,'4408690446873'),
       (1004,'07875288996'),
       (1005,'086678764889')
Developer technologies | Transact-SQL
SQL Server | Other
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 26,586 Reputation points
    2022-03-21T22:15:21.673+00:00

    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  |  
    +----------+----------------+-----+---------------+  
    
    2 people found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.