April 7, 2010 at 4:10 pm
Hi All,
I found this piece of code on the internet, which can split the comma delimited string into columns.
However, it only works which a fix column. If I entered extra data, it didn't work. I don't know much about UDF, can you please help me out?
create function [dbo].[f_GetEntryDelimiitted]
(
@line varchar(4000) ,
@fldnum int ,
@delim varchar(25) ,
@quoted varchar(1) -- Y/N
)
returns varchar(400)
as
begin
declare @i int ,
@j-2 int ,
@delimUsed varchar(25) ,
@S varchar(4000)
select @i = 1
while @fldnum > 0
begin
select @fldnum = @fldnum - 1
if substring(@line, @i, 1) = ',' and @Quoted = 'Y'
begin
select @delimUsed = ',' + @Delim ,
@i = @i + 1
end
else
begin
select @delimUsed = @Delim
end
select @j-2 = charindex(@delimUsed, @line, @i)
if @j-2 = 0
select @j-2 = datalength(@line) + 1
if @fldnum > 0
select @i = @j-2 +len(@delimused)
end
select @S = substring(@line,isnull(@i,1), @j-2 -@i)
return @S
end
/*create table #a (s varchar(1000))
insert #a select 'John,Smith,IT,2,sql,1,7'
insert #a select 'Fred,Jones,Assassin,7,Guns,5'
insert #a select 'Peter,Brown,Politics,23,minister,5'
insert #a select 'Peter,Brown,Politics,23,minister,5'
select a.* from
(
select Forname = dbo.f_GetEntryDelimiitted (s, 1, ',', 'y') ,
Surname = dbo.f_GetEntryDelimiitted (s, 2, ',', 'y') ,
Profession = dbo.f_GetEntryDelimiitted (s, 3, ',', 'Y') ,
ProfessionExp = dbo.f_GetEntryDelimiitted (s, 4, ',', 'Y') ,
Skill = dbo.f_GetEntryDelimiitted (s, 5, ',', 'Y') ,
SkillExp = dbo.f_GetEntryDelimiitted (s, 6, ',', 'Y'),
SkillExp2 = dbo.f_GetEntryDelimiitted (s, 7, '','N' )
from #a
) a
I wanted the result look like this
Forname Surname Profession ProfessionExp Skill SkillExp SkillExp2
John Smith IT 2 sql 1 7
Fred Jones Assassin 7 Guns 5
Peter Brown Politics 23 minister 5
Peter Brown Politics 23 minister 5
April 7, 2010 at 8:36 pm
See if this will work for you.
http://phelabaum.com/archive/2010/02/tally-table-delimited-list-to-table/
Edit: Sorry, just read that a bit closer, my function might be too hard to adapt.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply