Solved

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

Posted on 2009-07-16
9
937 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Upcoming Webinar: Securing your MySQL/MariaDB data

Join Percona’s Chief Evangelist, Colin Charles as he presents Securing your MySQL®/MariaDB® data on Tuesday, July 11, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

 

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

Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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…

724 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