Convert Single Field Into Fixed Number Of Columns Using Function

I would like to convert data from a field into a series of columns using a function. The object is to achieve a comma seperated file in a fixed format for import into a third party system.

The data would be typically Surname, Forename, Mailing Name, Address, Country, Post Code. However I would like the address which may contain any number of lines to be returned as seperate fields rather than a single column. The data has to be consistent across the entire export.

E.g.

Contact 1 Address= "Vicky Arms, Wellington Road, Cheswick"
Contact 2 Address = "No1, Armitage Apartments , Peninsula Way, Wickcombe"

Function would need to return seperate columns:

Address1                  Address2                  Address3                    Address4  
Vicky Arms               Wellington Road        Cheswick
No1                         Armitage Apartments Peninsula Way            Wickcombe

Code_ManiaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

imran_fastCommented:
customize this function

drop function dbo.CSV_Values
go
create function dbo.CSV_Values(@str varchar(1000))
returns
   @Table Table (value varchar(100))
 as
begin      
      
      declare @ptr as smallint,
            @counter smallint
      declare @substr varchar(1000)
      set @substr = ''
      set @ptr = 1
      set @counter = 0
      while @ptr <=len (@str)+1
            begin
            
            if (substring(@str, @ptr,  1) = ',' ) or @ptr = len(@str) +1
            begin

                  
                  insert into @table values(substring(@str,len(@substr)+1,@counter))      
                  set @substr = substring(@str,1,@ptr)
                  set @counter = 0              
            end
                  else
                  set @counter = @counter +1
                  set @ptr = @ptr + 1
            end
      Return
end

go


select * from dbo.csv_values('003,05,0007,5')
0
Code_ManiaAuthor Commented:
Thanks Imran, this needs to be in a column format rather than data rows though.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.