Split column into two columns and copy over values

Question

I have a Sybase table with a column storing char(6) values. I would like to drop this column and create two char(3) columns, copying over the first three chars of the original column to column 1 and the last 3 chars to column 2. What would be best way to do this?

Answer 1

ALTER TABLE YourTable ADD FirstHalf CHAR (3)
ALTER TABLE YourTable ADD SecondHalf CHAR (3)

UPDATE YourTable
SET
    FirstHalf = LEFT(OriginalColumn, 3),
    SecondHalf = RIGHT(OriginalColumn, 3)

ALTER TABLE YourTable DROP COLUMN OriginalColumn

Answer 2

Create your two new columns.

UPDATE the table with something like

UPDATE
  table
SET
  new1 = LEFT(old, 3),
  new2 = RIGHT(old, 3)

Remove the old column.