How to divide one column into 5 columns.

Question

How to divide one column into 5 columns.

HB-290559

Valued Member

Points: 57
More actions
January 9, 2007 at 12:12 pm

#95026

SQL 2000
I have one column with 250 length. It was supposed to be 5 different columns, 50-length each. But when it was created, those 5 columns were combined. Data is already in there, worth of about 3 years. I need to know how to separate one column into 5 columns and also keep existing data.
Any suggestions and comments are very appreciated. Thank you very much in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

We can't really help unless you provide some sample data and the required output. Once we have that we can get started on a solution.

HB-290559 Valued Member Points: 57 More actions · Answer 2

I apologize for not indicating details.

I like to divide Column1 (varchar(250),null) into 5 columns, making Column2, Columns3, Column4, Column5, Column6, making all varchar(50),null. We import Fixed-lengh text file into this table. Somehow, 5 of Fixed-Length fields are imported into Column1. We have been doing for 3+ years, so all the records in the past are based on this.

Now I need to spilit data in Column1 based on 50-Length and put them into Column2, Column3,...., Column6 respectively.

Column1

===================

abcdefghijklmnopqrstuvwxy

Column2 Column3 Column4 Column5 Column6

===== ===== ===== ===== =====

abcde fghij klmno pqrst uvwxy

I hope my explanation is good enough. Thank you for being patience and appreciate your comments.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Hmm unfortunately not.

How do I know when col1 ends and col2 starts?

Based on your data there's no way to be sure (unless you mean 5 characters and not 50)

Please provide an exemple using real data (or at least lengths).

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 4

If you are just want to extract the string into 50 character substrings, lookup in BOL for the SUBSTRING function.

If you have a delimiter in the string, you can specify start and/or length of the SUBSTRING using the CHARINDEX function (also documented in BOL).

Hope this helps

Mark

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 5

update table

set col1 = substring(col,1,50),

col1 = substring(col,51,100),

col1 = substring(col,101,150),

col1 = substring(col,151,200),

col1 = substring(col,201,250)

something like that

www.sql-library.com[/url]

HB-290559 Valued Member Points: 57 More actions · Answer 6

Thank you very much. Substring function worked great. I could create new columns, split data based on the length and input into new columns.

Again, I appreciate your time and comments. Thank you very much for being there for us.

HB

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I asked for sample data because you said the datatype was varchar(50) X 5.

Unless you are sure that all columns used the full 50 characters on every row you in fact cannot split the column without having a delimiter in place...

So can we see that sample data??

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Or actually more like :

update table

set col1 = substring(col,1,50),

col2 = substring(col,51,100),

col3 = substring(col,101,150),

col4 = substring(col,151,200),

col5 = substring(col,201,250)