How to Format Phone Numbers in SQL

Written by lysis
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Format Phone Numbers in SQL
(Hemera Technologies/AbleStock.com/Getty 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:
Easy

Other People Are Reading

Instructions

  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

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

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