Split a Column

Question

Post reply

Split a Column

Arthur Lorenzini

SSC-Addicted

Points: 447
More actions
July 13, 2007 at 3:03 pm

#120748

I have a column which is a VARCHAR(1000). Come to findout out client's data structure can only handle up to 255. How would I go about taking my VARCHAR(1000) column and splitting it out into smaller VARCHAR(255) columns?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

David McFarland SSChampion Points: 11815 More actions · Answer 1

You mean like this?

Col1 = Substring(MainColumn,1,250)

,Col2 = Substring(MainColumn,251,250)

,Col3 = Substring(MainColumn,501,250)

,Col4 = Substring(MainColumn,751,250)

Note, you can use left for the first string, but I used Substring to be consistent.

Arthur Lorenzini SSC-Addicted Points: 447 More actions · Answer 2

How would you run this?

In an ALTER TABLE STATEMENT?

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

Are you inserting your one field into the clients 4 already existing fields or are you adding the fields to the clients table or are you adding the 4 new fields to your table and then updating the records with new info or is this part of an application that needs to automatically populate the 4 new fields when the one field is inserted or updated?

Arthur Lorenzini SSC-Addicted Points: 447 More actions · Answer 4

I am adding 4 new column into an already existing table then updating them.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 5

use an alter table statement to add the 4 new fileds and then an update statement to set them as noted in the first post.

Arthur Lorenzini SSC-Addicted Points: 447 More actions · Answer 6

I got that to work but wht I need now is to Drop the original column (not a problem) but then rename one of the new column back to the original name but there is now RENAME in the ALTER TABLE statement.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 7

may not be the easiest way but you could drop the column, add it back in as 255 and then populate the data from col1 into the original with another update and then drop col1. Seems convoluted but for a one time thing it should work.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

Lookup sp_Rename in Books Online... that'll do what you need...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)