How to Format Phone Numbers in SQL

Written by lysis
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Format Phone Numbers in SQL
(Hemera Technologies/ Images)

Microsoft SQL Server allows programmers to create stored procedures. These stored procedures retrieve information from the database and send it to the Windows desktop or web page applications. Before you send the information to the application, you can format string variables. One common formatting function used by database programmers is adding the dashes and parentheses in a phone number. This can be done using the "Right()," "Left()" and "Substring()" functions.

Skill level:


  1. 1

    Set up a variable that holds a phone number. This variable can be a string passed by the program or one retrieved from the database. In this example, the phone number string is initialised and a customer's phone number is retrieved. The code is below:

    declare @phone varchar(20) set @phone = (select phone from customer where customerID = 1)

  2. 2

    Create a variable to hold the results. This variable will hold and return the phone number with the included dashes and parentheses. The code below creates a SQL variable for the result:

    declare @phone_final varchar(20)

  3. 3

    Retrieve and format the area code. This code places the area code in parentheses and assigns it to the @phone_final variable:

    set @phone_final = '(' + left(@phone, 3) + ') '

  4. 4

    Add the exchange to the result variable. The following code takes the exchange part of the phone number, adds a dash at the end and appends it to the @phone_final variable:

    set @phone_final = @phone_final + substring(@phone,4,3) + '-'

  5. 5

    Include the final four digits to the result variable. The following code appends the final 4 digits and prints the final result to your SQL window:

    set @phone_final = @phone_final + right(@phone, 4) print @phone_final

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.