Solved

Split 1 Column into Multiple Columns using SSIS/TSQL

Posted on 2010-11-22
8
2,159 Views
Last Modified: 2012-05-10
Hi Experts, I have a 2 Source columns called “GeneralInformation” and “PhoneNumber” and I need to split those two columns into multiple columns and should send them to target. Either I have to do it using SSIS or TSQL.

The first Source column “GeneralInformation” has to be split into 4 Columns like "Address1","City","State","Zip"

The Second column “PhoneNumber” has to be Split into 4 Columns “PHArea”,”PHexch”,”PHNbr”,”PHExt”.

Any Help would be Highly Appreciated.

Thanks In Advance.
 
 Source Sample:

GeneralInformation       PhoneNumber
'2155 East Chevy Road, Suite 301, Tucson, New york 85716-1111'      , (123) 456-7890
'2155 East Chevy Road, Suite 301, Tucson, New Jersey 85716' ,      (123) 456-7890 x22
'2155 East Chevy Road, Tucson, Arizona 85716-1111' ,       (123) 456-7890 x2285
'2155 East Chevy Road, Tucson, Pureto Rico 85716', (123) 456-7890 x3

Target Sample:

Address1      City      State      PostalCode      PhArea      PhExch      PhNbr      PhExt
2155 East Chevy Road, Suite 301      Tucson      New york      85716-1111
      123      456      7890      
2155 East Chevy Road, Suite 301      Tucson      New Jersey      85716      123      456      7890      22
2155 East Chevy Road      Tucson      Arizona      85716-1111      123      456      7890      2285
2155 East Chevy Road      Tucson      Pureto Rico      85716      123      456      7890      3
0
Comment
Question by:KuldeepReddy
[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
  • 4
  • 3
8 Comments
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 500 total points
ID: 34193632
/*
Here is a function to parse the address records.  Works for all of your examples but will probably need some tweeking for all of the data it might see.
*/

create function [dbo].[CSVToAddressFields]
(
      @GeneralInformation varchar(8000) ,
      @Delimiter      varchar(10)
)

--select      * from [dbo].[CSVToAddressFields] ('2155 East Chevy Road, Tucson, Pureto Rico 85716', ',')

returns @return table (AddressLine1 nvarchar(100), AddressLine2 nvarchar(100), City nvarchar(50), StateName nvarchar(50), ZipCode nchar(5), ZipPlus4 nchar(4))
as

begin

      declare @StateAndZipCode nvarchar(500)
      declare @City nvarchar(100)
      declare @AddressLine2 nvarchar(200)
      declare @AddressLine1 nvarchar(200)

      --parse State & Zip Code
      if charindex(@Delimiter, @GeneralInformation) > 1
      begin
            select @StateAndZipCode = ltrim(rtrim(reverse(left(reverse(@GeneralInformation), charindex(@Delimiter, reverse(@GeneralInformation)) -1))))
            select @GeneralInformation = REPLACE(@GeneralInformation, + @Delimiter + ' ' + @StateAndZipCode,'')
      end

      --parse City
      if charindex(@Delimiter, @GeneralInformation) > 1
      begin
            select @City = ltrim(rtrim(reverse(left(reverse(@GeneralInformation), charindex(@Delimiter, reverse(@GeneralInformation)) -1))))
            select @GeneralInformation = REPLACE(@GeneralInformation, + @Delimiter + ' ' + @City,'')
      end

      --parse Address Line 2
      if charindex(@Delimiter, @GeneralInformation) > 1
      begin
            select @AddressLine2 = ltrim(rtrim(reverse(left(reverse(@GeneralInformation), charindex(@Delimiter, reverse(@GeneralInformation)) -1))))
            select @GeneralInformation = REPLACE(@GeneralInformation, + @Delimiter + ' ' + @AddressLine2,'')
      end

      --parse Address Line 1
      if len(ltrim(rtrim(@GeneralInformation))) > 0
      begin
            select @AddressLine1 = ltrim(rtrim(@GeneralInformation))
      end
      else
      begin
            select @AddressLine1 = @AddressLine2
      end

      insert into @return

      select      
                  @AddressLine1
            ,      @AddressLine2
            ,      @City

            ,      REPLACE(@StateAndZipCode, RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))),'')      as      StateName
            ,      case len(LTRIM(RTRIM(RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))))))
                        when 5 then LEFT(LTRIM(RTRIM(RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))))),5)      
                        when 10 then LEFT(LTRIM(RTRIM(RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))))),5)      
                  end      as      ZipCode
            
            ,      case len(LTRIM(RTRIM(RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))))))
                        when 10 then RIGHT(LTRIM(RTRIM(RIGHT(@StateAndZipCode,CHARINDEX(' ', REVERSE(@StateAndZipCode))))),4)
                        else ''
                  end                                                                                                                  as      ZipPlus4
      
      return
end
0
 
LVL 6

Expert Comment

by:subhashpunia
ID: 34194787
In the FataFlow task, use the comma "," as delimiter and all values separated with comma will split in multiple columns.
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34195988
@subhashpunia

There are a variable number of columns based on the existence/non existence of Address Line 2.  I don't think Data Flow task can just process that since it varies.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 

Author Comment

by:KuldeepReddy
ID: 34197467
Hi Gohord, Thanks for your reply and the function is working amazingly. My Only Doubt is How can I use this in a sql select statement.
Suppose I have a table with 5 columns and one being the generalInformation column i have posted in the question.

I have a select statement like this

Select ID, FName, LName, PhoneNumber, GeneralInformation From MyTable WITH (NOLOCK)

How can I use the function to split the GeneralInformation Column using the function.

I have never worked on table return functions and dont know how to use them in a Query.

Pls Help me out in this.

Thanks Once again..
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34197533
use a cross apply:

select yourbase.*, yourAddr.*
from yourbasetable yourbase

cross apply dbo.YourFunction( yourbase.FieldWithAddressStuff) yourAddr
0
 

Author Comment

by:KuldeepReddy
ID: 34197641
Thanks Gohord.
0
 

Author Closing Comment

by:KuldeepReddy
ID: 34197648
This is a Fantastic Solution.
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34197680
Thanks KuldeepReddy!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

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.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 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