Solved

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

Posted on 2009-07-16
9
893 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query Help 12 52
SQL join help to a thrid table 51 76
How can I use SQL Stored Procedure to return FedEx Zone 17 41
Exchange 2010 Database wont mount 3 24
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…

911 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

20 Experts available now in Live!

Get 1:1 Help Now