Solved

Split 1 Column into Multiple Columns using SSIS/TSQL

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

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.

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 to calculate avaerage 21 41
Query Syntax 17 36
Query to capture 5 and 9 digit zip code? 4 22
Syntax for query to update table 2 14
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

803 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