Would like to split up a delimited field into columns (like Excel text to columns)

greetings everybody

please can me. looked on forum elsewhere solution, namely "split string using delimiter" thread , "how calculate least anmongst list of quarters" thread. no applied success. first thread looked promising unfortunately did not split field separate columns.

i have field called address, contains 1 to 4 lines separated | delimiter. wish split field 4 columns.

what have attempted incredibly messy , works (hence top 2 clause) breaks down when there 1 | delimiter. know there slicker , more simple way achieve aim.

thank in advance provide.

kind regards

paul

  select top 2	a.address,  				substring(a.address,1,charindex('|',a.address,1)-1) as address1,  				(len(substring(a.address,1,charindex('|',a.address,1)))+1) as start2,  				(charindex('|',a.address,1+len(substring(a.address,1,charindex('|',a.address,1))))-1) as end2,  (charindex('|',a.address,1+len(substring(a.address,1,charindex('|',a.address,1))))-1)-  (len(substring(a.address,1,charindex('|',a.address,1)))+1)+1 as len2,    				substring(a.address,(len(substring(a.address,1,charindex('|',a.address,1)))+1),  				(charindex('|',a.address,1+len(substring(a.address,1,charindex('|',a.address,1))))-1)-  (len(substring(a.address,1,charindex('|',a.address,1)))+1)+1)  				as address2  				    from     dbo.patientmaster as p    inner join dbo.t14_address as  on p.patientid=a.patientid    where chi is not null  and a.currentdemog = 'y'    and p.deceased = 0  

for particular case 4 delimiters go splitting , pivoting results.

i use function split values:

  /****** object: userdefinedfunction [dbo].[fnsplit]  script date: 02/08/2011 09:36:57 ******/ set ansi_nulls on go  set quoted_identifier on go   -- test query create function [dbo].[fnsplit] (@list varchar(8000),  @delim char(1) = ',' ) returns table as return   with csvtbl(start, stop) as (    select start = 1,       stop = charindex(@delim collate slovenian_bin2, @list + @delim)    union all    select start = stop + 1,       stop = charindex(@delim collate slovenian_bin2,                @list + @delim, stop + 1)    from  csvtbl    where stop > 0  )  select row_number() over (order by start) as id, ltrim(rtrim(substring(@list, start,            case when stop > 0 then stop - start else 0 end)))      as value  from  csvtbl  where stop > 0  go   

now, having function can 

 

 

  ;with cte as (select addressid, address, f.id, f.value from addresses   cross apply dbo.fnsplit(address, '|') f)  select addressid, [1] as address1, [2] as address2, [3] as address3, [4] as address4 from cte pivot (max(value) for id in ([1],[2],[3],[4])) pvt  

 

 

---

premature optimization root of evil in programming. (c) donald knuth

naomi nosonovsky, sr. programmer-analyst

my blog

SQL Server  >  Transact-SQL