April 7, 2010 at 4:12 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-2 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-2 = substring(@line,isnull(@i,1), @j-2 -@i)
return@s-2
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
FornameSurnameProfessionProfessionExpSkillSkillExpSkillExp2
JohnSmithIT2sql17
FredJonesAssassin 7Guns5
PeterBrownPolitics 23minister5
PeterBrownPolitics23minister5
The code above did not populate SkillExp2 for me.
Thanks in advance.
April 7, 2010 at 5:05 pm
Duplicate Post.
Direct answers to
http://www.sqlservercentral.com/Forums/Topic899069-338-1.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply