CODEWITHSUNDEEP
sqlsql-server
user1096207
user1096207

Reputation: 157

Formatting existing SQL Data?

I have a table full of a contacts information, and I want to change the phone number from 5555555555 to (555) 555-5555, instead.

I've found a few things online for selecting data and having it be returned in that format, but no way to actually update the records to use the new format. Is this even possible?

Upvotes: 1

Views: 69

Answers (3)

Jordan
Jordan

Reputation: 530

If you want to transfer the old data format to the new one just make loop that read the cell change it to the format that you like and store it back in the cell.

string NewString;
NewString = "(" + TheOriginalString.Substring(0,3) + ") ";
NewString += TheOriginalString.Substring(3,3) + "-";   
NewString += TheOriginalString.Substring(6);

Then just store the new string in the same cell.
The example code is in C#.

Upvotes: 0

user596075
user596075

Reputation: 

update YourTable
set PhoneNumber =
    '(' + LEFT(PhoneNumber, 3) +
    ') ' + SUBSTRING(PhoneNumber, 3, 3) +
    '-' + RIGHT(PhoneNumber, 4)

This will update your data with the correct format as you specified. This works with SQL Server, but I can't speak for any other RDBMS.

Upvotes: 2

Elastep
Elastep

Reputation: 3408

it's a bad practice doing so in db. Good practice is to store raw data in your db and to format it when you're selecting it or even better - when you show this data to user.

Upvotes: 4

Related Questions