Here are some examples of formatting phone numbers in SQL Server.

This includes examples of formatting numbers in E.164 format (for international numbers), prepending the country code and area code, as well as omitting leading zero from the country code when required.

Numeric Phone Numbers

If the phone number is stored as a numeric value (which it shouldn’t be), you can use the FORMAT() function to format it as phone number.

Example:

SELECT FORMAT(0234567890, '000-000-0000');

Result:

023-456-7890

The first argument is the phone number and the second argument is the format string. In this example I’m using a custom format string. You can adjust the format string to suit the desired phone number format:

SELECT FORMAT(0234567890, '(000) 000-0000');

Result:

(023) 456-7890

It’s important to know what the format strings actually do. When using zeros, you’ll need to make sure that the phone number actually has digits in every place where there’s a zero format specifier (otherwise you might accidentally add zeros to the number).

You’ll also need to ensure that there’s a format specifier to match each digit (otherwise you’ll delete digits from the phone number).

Another way to express the format string is with the # format specifier. However, this will result in any leading zeros being removed from the phone number.

Here’s an example to illustrate what I mean:

SELECT 
    FORMAT(0234567890, '000-000-0000') AS "000-000-0000",
    FORMAT(0234567890, '###-###-####') AS "###-###-####";

Result:

+----------------+----------------+
| 000-000-0000   | ###-###-####   |
|----------------+----------------|
| 023-456-7890   | 23-456-7890    |
+----------------+----------------+

The FORMAT() function only accepts numeric types and datetime values. If the input phone number isn’t actually a numeric type, then you’ll probably get an error, something like this:

SELECT FORMAT('0234567890', '000-000-0000');

Result:

Msg 8116, Level 16, State 1, Line 1
Argument data type varchar is invalid for argument 1 of format function.

In such cases, it’s easy enough to convert the value to a numeric type:

SELECT FORMAT(CAST('0234567890' AS int), '000-000-0000');

Result:

023-456-7890

But phone numbers shouldn’t be stored as numeric types anyway.

Numeric values can be rounded up or down, have calculations performed on them, have insignificant zeros automatically removed, etc.

Phone numbers are a fixed value. Every digit is significant (including leading zeros). We don’t want leading zeros to go missing unless we explicitly require this (for a country code for example). And we don’t want our phone numbers to be inadvertently rounded up or down. And it’s unlikely you’ll ever need to perform calculations on your phone numbers.

So it therefore makes more sense to store phone numbers as a string. Converting them to a numeric type before formatting them (like in the above example) could still result in unexpected changes being done to the number.

If the phone number is already a string, then try the following method.

Phone Numbers Stored as Strings

If the phone number is stored as a string, you can use the STUFF() function to insert the appropriate strings into the phone number at the relevant places.

Examples:

SELECT
    STUFF(STUFF('0234567890', 7, 0, '-'), 4, 0, '-') AS "Format 1",
    STUFF(STUFF(STUFF('0234567890', 7, 0, '-'), 4, 0, ') '), 1, 0, '(') AS "Format 2";

Result:

+--------------+----------------+
| Format 1     | Format 2       |
|--------------+----------------|
| 023-456-7890 | (023) 456-7890 |
+--------------+----------------+

The first argument is the original string (in this case, the phone number), and the fourth argument is the string to insert. The second argument specifies where to insert the fourth argument.

The third argument specifies how many characters to delete from the original string (in case you want to replace certain characters with the new string). In our case, we don’t want to delete any characters, and so we use 0.

Depending on the format of the original phone number, another way to do it is to use the REPLACE() function. An example of where this could be useful is when the phone number is already formatted with a separator, but it needs to be replaced with another separator:

SELECT REPLACE('023 456 7890', ' ', '-');

Result:

023-456-7890

International Numbers

E.164 is an international standard that defines the format for international telephone numbers.

E.164 numbers are formatted [+][country code][area code][local phone number] and can have a maximum of fifteen digits.

Here’s an example that uses two methods to concatenate the country code, the area code, and the phone number:

SELECT 
    CONCAT('+', '1', '415', '4567890') AS 'CONCAT() Function',
    '+' + '1' + '415' + '4567890' AS 'Concatenation Operator';

Result:

+---------------------+--------------------------+
| CONCAT() Function   | Concatenation Operator   |
|---------------------+--------------------------|
| +14154567890        | +14154567890             |
+---------------------+--------------------------+

The first method uses the CONCAT() function, and the second uses the concatenation operator (+).

That example formats a US based number. In many countries, the area code has a leading zero that needs to be dropped when using the E.164 format.

One way to suppress any leading zeros is to convert the area code to a numeric value and back again.

Here’s an example of using that technique on a UK based number:

SELECT CONCAT(
        '+', 
        '44', 
        CAST(CAST('020' AS int) AS varchar(3)), 
        '34567890'
        );

Result:

+442034567890

In this case the leading zero was dropped.

Here’s the same code run against the previous US based number (which doesn’t use a leading zero in the country code):

SELECT CONCAT(
        '+', 
        '1', 
        CAST(CAST('415' AS int) AS varchar(3)), 
        '4567890'
        );

Result:

+14154567890

This time the country code remained at three digits.