|
rockmoose
SQL Natt Alfen
3279 Posts |
 Posted - 2005-02-21 : 12:01:42
|
[code]create table #csv(csvtext varchar(8000) not null)
insert #csv select 'a,b,c,d,e,f,g' union select '1,2,3,4,5,6'
select
dbo.fnGetCsvPart(csvtext,0,default) as csv_pos0
,dbo.fnGetCsvPart(csvtext,2,default) as csv_pos2
,dbo.fnGetCsvPart(csvtext,2,1) as all_csv_from_pos2
from #csv
csv_pos0 csv_pos2 all_csv_from_pos2
------------------------------ ------------------------------ ------------------------------
1 3 3,4,5,6
a c c,d,e,f,g
create function dbo.fnGetCsvPart(@csv varchar(8000),@index tinyint, @last bit = 0)
returns varchar(4000)
as
/* function to retrieve 0 based "column" from csv string */
begin
declare @i int; set @i = 0
while 1 = 1
begin
if @index = 0
begin
if @last = 1 or charindex(',',@csv,@i+1) = 0
return substring(@csv,@i+1,len(@csv)-@i+1)
else
return substring(@csv,@i+1,charindex(',',@csv,@i+1)-@i-1)
end
select @index = @index-1, @i = charindex(',',@csv,@i+1)
if @i = 0 break
end
return null
end
GO
[/code]
rockmoose |
|
fnGetCsvPart