Remove trailing zeros from phone number-CodePudding

I have an old Phone table which has a PhonoNo column with length 15. Now I am working on a task for phone number cleanup with trailing 0s after 10 digits.

 ---------------- --------------- 
|    PhoneNo     | Desire output |
 ---------------- --------------- 
|198765432100000 |    1987654321 |
|   198765432100 |    1987654321 |
|     1987653210 |    1987653210 |
|    19876543210 |    1987654321 |
|198765432100100 | 1987654321001 |
|       19876543 |      19876543 |
|      009876543 |     009876543 |
 ---------------- ---------------

I am using the below code but it does not work for me.

SELECT PhoneNo, REPLACE(LTRIM(REPLACE(PhoneNo,'0',' ')),' ','0') as NewPhoneNo 
FROM tblPhone WITH(NOLOCK)

I am looking for a solution in SQL Server to remove trailing 0s without creating any function.

CodePudding user response：

A possible option is the TRIM() function. By default the TRIM() function removes the space character or other specified characters from the start and end of a string, so you need to put an additional character (# in the example) at the correct positions (1st and 10th) and remove it after that:

SELECT 
   REPLACE(
      TRIM('0' FROM '#'   SUBSTRING(PhoneNo, 1, 10)   '#'   SUBSTRING(PhoneNo, 11, 5)),
      '#',
      ''
   ) AS PhoneNo   
FROM (VALUES
   ('19876543210000'),
   ('198765432100'),
   ('1987653210'),
   ('19876543210'),
   ('198765432100100'),
   ('19876543'),
   ('009876543')
) t (PhoneNo)

Result:

PhoneNo
-------------
1987654321
1987654321
1987653210
1987654321
1987654321001
19876543
009876543

CodePudding user response：

You could use a CASE expression to return values with less than or equal to 10 characters as they are, then then REPLACE and RTRIM to remove the trailing 0's:

SELECT CASE WHEN LEN(PhoneNo) <= 10 THEN PhoneNo
            ELSE REPLACE(RTRIM(REPLACE(PhoneNo,'0',' ')),' ','0')
       END
FROM (VALUES ('198765432100000'),
             ('198765432100'),
             ('1987653210'),
             ('19876543210'),
             ('198765432100100'),
             ('19876543'),
             ('009876543'))V(PhoneNo);

CodePudding user response：

Can you try the flowing SQL code, let me know if this works for you

SELECT *, cast(PhoneNo as float) as Desire_Output FROM [database]