CODEWITHSUNDEEP
sqlsql-server
heisjef
heisjef

Reputation: 89

Microsoft SQL Server - Copy column in table, modify value and type

I'm quite new to scripts and need some help on creating a script doing multiple actions in sequence.

Let's use this tentative (and bisarre) table to illustrate the solution. The table could potentially hold hundreds of entries:

Original table

  1. I want to add a PhoneNumber column with varchar type and populate it from the existing PhoneNumber column.
  2. For phone numbers consiting of 5 digits I want to add a leading zero (0) so all entries have the same length (6).
  3. When this is done for all occurances of 5-digit phone numbers I want to delete the old PhoneNumber column

This is how the table should look after step 1: After step 1

And after step 2 it should look like this:

After step 2

Finally, after the third step I want this outcome:

Final result

Can this be accomplished with a script? I don't really need the script to follow this exact sequence as long as it results in the desired outcome. This is just the sequence of actions I have thought could be an allright approach.

Upvotes: 0

Views: 62

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269573

If the value is really an integer, you can use format():

select id, phonenumber, format(phonenumber, '000000')
from t;

You may want to add this as a computed column:

alter table t add phonenumber_6 as (format(phonenumber, '000000'));

Upvotes: 1

Esteban
Esteban

Reputation: 146

Here you go

Remember, you can't have 2 columns called the same

ALTER TABLE [TABLENAME] ADD PHONENUMBERCHAR char(6); --Adds a new column
GO;
UPDATE [TABLENAME] SET PHONENUMBERCHAR = RIGHT(CONCAT('000000', PHONENUMBER), 6); --Updates the value
GO;
ALTER TABLE [TABLENAME] DROP COLUMN PHONENUMBER --Deletes the old column

Upvotes: 3

Related Questions