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:
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)
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)
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) + ') '
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) + '-'
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
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for