Split 1 Column into Multiple Columns using SSIS/TSQL

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
KuldeepReddyAsked:
Who is Participating?
 
Christopher GordonConnect With a Mentor Senior Developer AnalystCommented:
/*
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
 
subhashpuniaCommented:
In the FataFlow task, use the comma "," as delimiter and all values separated with comma will split in multiple columns.
0
 
Christopher GordonSenior Developer AnalystCommented:
@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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
KuldeepReddyAuthor Commented:
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
 
Christopher GordonSenior Developer AnalystCommented:
use a cross apply:

select yourbase.*, yourAddr.*
from yourbasetable yourbase

cross apply dbo.YourFunction( yourbase.FieldWithAddressStuff) yourAddr
0
 
KuldeepReddyAuthor Commented:
Thanks Gohord.
0
 
KuldeepReddyAuthor Commented:
This is a Fantastic Solution.
0
 
Christopher GordonSenior Developer AnalystCommented:
Thanks KuldeepReddy!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.