Solved

Split 1 Column into Multiple Columns using SSIS/TSQL

Posted on 2010-11-22
8
2,148 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

790 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