What is the best way to turn phone field data for example 1112223333 into the format of 111-222-3333?
- This will be best accomplished by running SQL queries to the table through SQL Management Studio.
- Assuming all the field's values are the same format to begin with (1112223333) and the field's name is Phone on the Subset table
- Use the below SUBSTRING query.
The context of the SUBSTRING function is as follows:
CAUTION: Be sure to have a full backup of the database prior to running any kind of UPDATE statement as there is no roll back option!
The following query when run in SQL Management Studio will take a Subset.Phone field value of 1112223333 and change it to 111-222-3333:
SET PHONE = SUBSTRING(PHONE,1,3) + '-' + SUBSTRING(PHONE,4,3) + '-' + SUBSTRING(PHONE, 7,4) WHERE (PHONE NOT LIKE '%-%' AND PHONE <> '')
If the scenario is the value of 1112223333 needs to be changed to a format of (111) 222-3333, then the following query will accomplish this change:
SET Phone = '(' + SUBSTRING(Phone,1,3) + ')' + ' ' + SUBSTRING(Phone,4,3) + '-' + SUBSTRING(Phone, 7,4) WHERE (Phone NOT LIKE '%(%' AND Phone <> '')