Improve company productivity with a Business Account.Sign Up

x
?
Solved

Split one colunm into many cells using T-SQL or SSIS

Posted on 2009-07-16
9
Medium Priority
?
956 Views
Last Modified: 2012-05-07
Hi there,
i am looking for some help on the following,
I have a cell of information like this:
7 Richview Office Park, Clonskeagh, Dublin 14, Dublin
Now this will go into one cell of data for example in Excel.
I want to seperate this out into wat ever amount of cells is needed.
So with this example i will need 4 cells to hold each piece of info (the comma is the seperater.)

In Excel if i were copying this data i could use something like the  text to colum function. But i don't know how to do this using T-SQL

so the infomration in my table will be two colimns
One with the Customer number
One with the Address.

The address can have different amount of info in it.

for example some address might just be No 11, Dublin
and others will be longer.

I would like the address cell to be split out per comma.

So for No11, Dublin this would be two cells.

Can anyone give me some advice on how to do this?

Thanks,
putoch
Drop Table CUSTOMER_ADDRESS_DUMP
CREATE TABLE CUSTOMER_ADDRESS_DUMP
(id int null,
all_address VARCHAR(2000),
Address_1 VARCHAR(200),
Address_2 VARCHAR(200),
Address_3 VARCHAR(200),
Address_4 VARCHAR(200),
City VARCHAR(50),
County VARCHAR(50),
POST_CODE VARCHAR(20),
COUNTRY VARCHAR(20));
 
 
INSERT INTO CUSTOMER_ADDRESS_DUMP(id,all_address)
VALUES (10001055,'Drumhaughley, Killoe, Co. Longford')
 
INSERT INTO CUSTOMER_ADDRESS_DUMP (id,all_address) VALUES (100741,'Unit T28 Stillorgan Industrial, Rowan Avenue, Stillorgan, Blackrock, Dublin, Dublin')
 
--This will give you the first word before a comma
UPDATE CUSTOMER_ADDRESS_DUMP
SET Address_1 = LEFT(all_address,CHARINDEX(',',all_address)-1)
 
 
-- This will give you the second one. 
UPDATE CUSTOMER_ADDRESS_DUMP
SET Address_2 = LEFT(
RIGHT(all_address,LEN(all_address)-CHARINDEX(',',all_address)) ,
CHARINDEX(',',RIGHT(all_address,LEN(all_address)-CHARINDEX(',',all_address)))-1)
 
 
--Trying to get the remaining but not sure how to , need help please:
 
-- This iwll give you the third Entry 
UPDATE CUSTOMER_ADDRESS_DUMP
SET Address_3 = RIGHT(all_address,LEN(all_address)-CHARINDEX(',',all_address)-CHARINDEX(',',all_address))

Open in new window

0
Comment
Question by:Putoch
  • 5
  • 4
9 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24868951
0
 

Author Comment

by:Putoch
ID: 24869020
Thanks Ralmada, could you give me some advice with this function as i'm finding it very hard to understand? i'm looking at angll's answer

thanks
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24869449
Ok, so let's say you have a table with two columns, id and address (with the full address), then, after you create the function above, you can use this query to pivot it. Please note that I'm assuming you will have up to 8 columns:

select id, [1], [2], [3], [4], [5], [6], [7], [8]
from
(select a.id, b.value, row_number() over (partition by id order by id) rn from 
CUSTOMER_ADDRESS_DUMP a
cross apply dbo.ParmsToList(a.Address, ',') b) o
pivot (max(value) for rn ([1], [2], [3], [4], [5], [6], [7], [8])) p

Open in new window

0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 

Author Comment

by:Putoch
ID: 24869873
sorry for not picking up on this, but when i run both the function and this query i get an error
Incorrect syntax near '('.
and i've checked your code to see if there was syntax errors..
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24870962
Yeah, missed "in" in line 6:

select id, [1], [2], [3], [4], [5], [6], [7], [8]
from
(select a.id, b.Value, row_number() over (partition by id order by id) rn from
CUSTOMER_ADDRESS_DUMP a
cross apply dbo.ParmsToList(a.Address, ',') b) o
pivot (max(value) for rn in ([1], [2], [3], [4], [5], [6], [7], [8])) p
And your function should be like this:

Create FUNCTION [dbo].[ParmsToList] (@Parameters varchar(500), @delimiter varchar(10) )
returns @result TABLE (Value varchar(100))
AS  
begin
    declare @dx varchar(9)
    -- declare @loops int
     --set @loops = 0
 
     DECLARE @TempList table
          (
          Value varchar(100)
          )
 
     if @delimiter is null  set @delimiter = ' '
     if len(@delimiter) < 1 set @delimiter = ' '
     set @dx = left(@delimiter, len(@delimiter)-1)
 
     DECLARE @Value varchar(8000), @Pos int
 
     SET @Parameters = LTRIM(RTRIM(@Parameters))+ @delimiter
     SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
     IF REPLACE(@Parameters, @delimiter, @dx) <> ''
     BEGIN
          WHILE @Pos > 0 -- AND @Loops < 100
          BEGIN
               --set @loops = @loops + 1
               SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (Value) VALUES (CAST(@Value AS varchar)) --Use Appropriate conversion
               END
               SET @Parameters = SUBSTRING(@Parameters, @Pos+ len(@delimiter), 8000)
               SET @Pos = CHARINDEX(@delimiter, @Parameters, 1)
 
          END
     END    
     INSERT @result
     SELECT value
        FROM @TempList
     RETURN
END     

Open in new window

0
 

Author Comment

by:Putoch
ID: 24871134
Thank you and sorry , i'm trying to understand your sytax, sorry about this.
when i run the your query i get an incorrect syntax near the keyword select.
Because i am finding it hard ot understand the sytaz logic i don't knwo why its throwing this error
Would you be able to explane it please?
thanks,
putoch
0
 

Author Comment

by:Putoch
ID: 24871161
Sorry i removed the o and i can see whats happeneing, the query returns the results row after row, you use row_number to give  aproper sequence, and then use a pivot to stage the results correctly in the format i needed.
Thanks so much for you help and advice!!
putich!
0
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 24871782
Yeah, don't remove the alias "o" from there it's just part of the PIVOT.
So basically, the function will split the full address and put each section (separated with a comma in this case) into a table variable. Then I used the cross apply functionality to "join" the table produced by the function with your main table. This is a new functionality in SQL 2005 and it's quite handy to join a table to a table-valued-function. More info on this here:
http://www.sqlteam.com/article/using-cross-apply-in-sql-server-2005 
Also I'm using row_number to give proper sequence as you've already figured out.
Finally I'm using PIVOT to display what was in rows into columns.
0
 

Author Closing Comment

by:Putoch
ID: 31604208
Thanks Raimada
0

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

606 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question