Phone Formatting Function in SQL Server

Hello to everyone,

In this article, I will provide information about the phone formatting function in SQL Server.

In SQL Server, in some cases, you may want to format and display phone numbers in order to make them more readable.

You can easily do this by using the function below.

--Fonksiyonun oluşturulması

CREATE FUNCTION PhoneFormat
(
    @Phone NVARCHAR(20)
)
RETURNS NVARCHAR(20)
AS
BEGIN
    DECLARE @PhoneFormat NVARCHAR(20);
    SET @PhoneFormat
        = (CASE
               WHEN LEN(REPLACE(@Phone, ' ', '')) = 10 THEN
                   LEFT(REPLACE(@Phone, ' ', ''), 3) + '-' + SUBSTRING(REPLACE(@Phone, ' ', ''), 4, 3) + '-'
                   + SUBSTRING(REPLACE(@Phone, ' ', ''), 7, 2) + '-' + RIGHT(REPLACE(@Phone, ' ', ''), 2)
               WHEN LEN(REPLACE(@Phone, ' ', '')) = 11 THEN
                   SUBSTRING(REPLACE(@Phone, ' ', ''), 2, 3) + '-' + SUBSTRING(REPLACE(@Phone, ' ', ''), 5, 3) + '-'
                   + SUBSTRING(REPLACE(@Phone, ' ', ''), 8, 2) + '-' + RIGHT(REPLACE(@Phone, ' ', ''), 2)
               WHEN LEN(REPLACE(@Phone, ' ', '')) = 7 THEN
                   LEFT(REPLACE('000' + @Phone, ' ', ''), 3) + '-' + SUBSTRING(REPLACE('000' + @Phone, ' ', ''), 4, 3)
                   + '-' + SUBSTRING(REPLACE('000' + @Phone, ' ', ''), 7, 2) + '-'
                   + RIGHT(REPLACE('000' + @Phone, ' ', ''), 2)
               ELSE
                   @Phone
           END
          );

    RETURN @PhoneFormat;

END;


--Kullanımı

SELECT dbo.PhoneFormat('05556669988')
SELECT dbo.PhoneFormat('5556669988')

When you create the function and run the code, you will see a result similar to the one below.

As you can see, phone numbers are shown in a formatted format.

Good luck to everyone in business and life.