Solved

Split 1 Column into Multiple Columns using SSIS/TSQL

Posted on 2010-11-22
8
2,132 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

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