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

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

PutochAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PutochAuthor Commented:
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
ralmadaCommented:
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
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

PutochAuthor Commented:
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
ralmadaCommented:
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
PutochAuthor Commented:
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
PutochAuthor Commented:
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
ralmadaCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PutochAuthor Commented:
Thanks Raimada
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.