One Delimited Column To 8 Columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

One Delimited Column To 8 Columns

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-11-11 : 11:24:05

I have a table which stores value delimited using "," I need to split the delimited column value to 8 columns.
Whats the best way? I was thinking about UDF any other solutions?

SampleCol
ABC,XYZ,DEF,123,678,ZXY,,

ExpectedResult
col1 col2 col3 col4 col5 col6 col7 col8
ABC XYZ DEF 123 678 ZXY

Thanks

Karunakaran

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-11-11 : 11:39:29

Is it "0th normal form" day today?

Is this a table that you have control over? Have you consider properly storing the data as rows in a related table?

Kristen
Test

22859 Posts

Posted - 2005-11-11 : 12:19:17

[code]
DECLARE @SampleCol varchar(100),
@I1 int,
@I2 int,
@I3 int,
@I4 int,
@I5 int,
@I6 int,
@I7 int,
@I8 int,
@COLUMN_1 varchar(10),
@COLUMN_2 varchar(10),
@COLUMN_3 varchar(10),
@COLUMN_4 varchar(10),
@COLUMN_5 varchar(10),
@COLUMN_6 varchar(10),
@COLUMN_7 varchar(10),
@COLUMN_8 varchar(10)
SET @SampleCol = 'ABC,XYZ,DEF,123,678,ZXY,,'

SELECT
@I1 = CHARINDEX(',', @SampleCol + ',')
, @COLUMN_1 = SUBSTRING(@SampleCol + ',', 1, @I1-1)
, @I2 = CASE WHEN COALESCE(@I1, 0) = 0 THEN 0 ELSE COALESCE(CHARINDEX(',', @SampleCol + ',', @I1+1), DATALENGTH(@SampleCol + ',')) END
, @COLUMN_2 = CASE WHEN @I2 = 0 THEN NULL ELSE SUBSTRING(@SampleCol + ',', @I1+1, @I2-@I1-1) END
, @I3 = CASE WHEN COALESCE(@I2, 0) = 0 THEN 0 ELSE COALESCE(CHARINDEX(',', @SampleCol + ',', @I2+1), DATALENGTH(@SampleCol + ',')) END
, @COLUMN_3 = CASE WHEN @I3 = 0 THEN NULL ELSE SUBSTRING(@SampleCol + ',', @I2+1, @I3-@I2-1) END
, @I4 = CASE WHEN COALESCE(@I3, 0) = 0 THEN 0 ELSE COALESCE(CHARINDEX(',', @SampleCol + ',', @I3+1), DATALENGTH(@SampleCol + ',')) END
, @COLUMN_4 = CASE WHEN @I4 = 0 THEN NULL ELSE SUBSTRING(@SampleCol + ',', @I3+1, @I4-@I3-1) END
, @I5 = CASE WHEN COALESCE(@I4, 0) = 0 THEN 0 ELSE COALESCE(CHARINDEX(',', @SampleCol + ',', @I4+1), DATALENGTH(@SampleCol + ',')) END
, @COLUMN_5 = CASE WHEN @I5 = 0 THEN NULL ELSE SUBSTRING(@SampleCol + ',', @I4+1, @I5-@I4-1) END
, @I6 = CASE WHEN COALESCE(@I5, 0) = 0 THEN 0 ELSE COALESCE(CHARINDEX(',', @SampleCol + ',', @I5+1), DATALENGTH(@SampleCol + ',')) END
, @COLUMN_6 = CASE WHEN @I6 = 0 THEN NULL ELSE SUBSTRING(@SampleCol + ',', @I5+1, @I6-@I5-1) END
, @I7 = CASE WHEN COALESCE(@I6, 0) = 0 THEN 0 ELSE COALESCE(CHARINDEX(',', @SampleCol + ',', @I6+1), DATALENGTH(@SampleCol + ',')) END
, @COLUMN_7 = CASE WHEN @I7 = 0 THEN NULL ELSE SUBSTRING(@SampleCol + ',', @I6+1, @I7-@I6-1) END
, @I8 = CASE WHEN COALESCE(@I7, 0) = 0 THEN 0 ELSE COALESCE(CHARINDEX(',', @SampleCol + ',', @I7+1), DATALENGTH(@SampleCol + ',')) END
, @COLUMN_8 = CASE WHEN @I8 = 0 THEN NULL ELSE SUBSTRING(@SampleCol + ',', @I7+1, @I8-@I7-1) END
SELECT @COLUMN_1, @COLUMN_2, @COLUMN_3, @COLUMN_4, @COLUMN_5, @COLUMN_6, @COLUMN_7, @COLUMN_8
[/code]
Kristen

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-11-11 : 12:41:03

quote:
Originally posted by jsmith8858
Is this a table that you have control over? Have you consider properly storing the data as rows in a related table?

No, I dont have any control over that table on how the data is stored, because thats how the client is storing data. All I have is the permission to use that data

. I have to split this column to write to a text file along with other columns.

Thanks for the solution Kristen. I'll work it out and post the outcome.

Thanks

Karunakaran

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources