Solved

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

Posted on 2009-07-16
9
880 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
 

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now