SDU Tools: Format Australian Phone Number

Our free SDU Tools for developers and DBAs, now includes a very large number of tools, with procedures, functions, and views. One request that we had some while back, was the ability to format phone numbers using Australian phone number format. To make that easy, we added the FormatAustralianPhoneNumber function.

The only parameter for this function is the phone number that needs to be formatted.

It starts by finding any digits. Then if it finds either 6, 7, 8, or (the standard) 10 digits, it formats the number appropriately.

If the values starts with a + (i.e. a country code) and the country code is not +61 (Australia), then it leaves the phone number unchanged.

Find out more

You can see it in action in the main image above, and in the video here. The full current version of the code is also shown below:

https://youtu.be/J4SIZI52hO4

You can use our tools as a set or as a great example of how to write functions like these.

Access to SDU Tools is one of the benefits of being an SDU Insider, along with access to our other free tools and eBooks. Please just visit here for more info:

http://sdutools.sqldownunder.com

Latest version of the code

Note: the code might wrap when displayed below.

CREATE FUNCTION [SDU_Tools].[FormatAustralianPhoneNumber]
(
    @PhoneNumberToFormat varchar(max)
)
RETURNS varchar(20)
AS
BEGIN

-- Function:      Format as an Australian Phone Number
-- Parameters:    @PhoneNumberToFormat varchar(max)
-- Action:        Formats a string as an Australian Phone Number
-- Return:        nvarchar(max)
-- Refer to this video: https://youtu.be/J4SIZI52hO4
--
-- Test examples:     
    /*
        SELECT SDU_Tools.FormatAustralianPhoneNumber('0419929232'),
               SDU_Tools.FormatAustralianPhoneNumber('+61 419292323'),
               SDU_Tools.FormatAustralianPhoneNumber('9902ABC232'),
               SDU_Tools.FormatAustralianPhoneNumber('92932323');
    */

    DECLARE @FormattedPhoneNumber varchar(20) = '';
    DECLARE @TrimmedPhoneNumber varchar(20) = LEFT(RTRIM(LTRIM(REPLACE(@PhoneNumberToFormat, '+61', '0'))), 20);

    IF @PhoneNumberToFormat IS NOT NULL 
    BEGIN
        DECLARE @Counter int = 1;
        DECLARE @NumbersOnly varchar(20) = '';

        WHILE @Counter <= LEN(@TrimmedPhoneNumber)
        BEGIN
            IF SUBSTRING(@TrimmedPhoneNumber, @Counter, 1) BETWEEN '0' AND '9'
            BEGIN
                SET @NumbersOnly += SUBSTRING(@TrimmedPhoneNumber, @Counter, 1);
            END;
            SET @Counter += 1;
        END;

        SET @FormattedPhoneNumber 
            = CASE LEN(@NumbersOnly)
                   WHEN 6 THEN SUBSTRING(@NumbersOnly, 1, 3) + '-' + SUBSTRING(@NumbersOnly, 4, 3)
                   WHEN 7 THEN SUBSTRING(@NumbersOnly, 1, 3) + '-' + SUBSTRING(@NumbersOnly, 4, 4)
                   WHEN 8 THEN SUBSTRING(@NumbersOnly, 1, 4) + '-' + SUBSTRING(@NumbersOnly, 5, 4)
                   WHEN 10 THEN '(' + SUBSTRING(@NumbersOnly, 1, 2) + ') '
                                + SUBSTRING(@NumbersOnly, 3, 4) + '-'
                                + SUBSTRING(@NumbersOnly, 7, 4)
                   ELSE LTRIM(RTRIM(@PhoneNumberToFormat))
              END;
    END;

    RETURN UPPER(@FormattedPhoneNumber);
END;

2025-02-09