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?
 
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
All Courses

From novice to tech pro — start learning today.