Splitting Comma Delimited String into Columns

• joemai

  Hall of Fame

  Points: 3496

  More actions

  April 7, 2010 at 4:12 pm

  #213497

  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.

• SQLRNNR

  SSC Guru

  Points: 281334

  More actions

  April 7, 2010 at 5:05 pm

  #1147879

  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